MySQL GROUP BY Aggregate Functions: Tutorial, Examples, FAQs & Interview Tips

MySQL GROUP BY Aggregate Functions

MySQL in MySQL is best learned by connecting the rule to an order-management database. Start with the smallest SQL statement, observe the output, and then add one realistic constraint so the concept becomes practical.

The key habit for this lesson is to watch table rows, constraints, and indexes as it changes. That makes the topic easier to debug, easier to explain in interviews, and easier to use in real code without memorizing isolated syntax.

GROUP BY and Aggregate Functions

The GROUP BY clause groups rows that share the same value in one or more columns, then applies aggregate functions to each group. Common aggregate functions are COUNT, SUM, AVG, MIN, MAX, and GROUP_CONCAT.

GROUP BY with Aggregate Functions

-- Count products per category
SELECT category, COUNT(*) AS product_count
FROM products
GROUP BY category;

-- Total stock and average price per category
SELECT
    category,
    COUNT(*)          AS product_count,
    SUM(stock)        AS total_stock,
    AVG(price)        AS avg_price,
    MIN(price)        AS min_price,
    MAX(price)        AS max_price
FROM products
GROUP BY category
ORDER BY product_count DESC;

-- Total revenue per order
SELECT
    order_id,
    SUM(quantity * unit_price) AS order_total,
    COUNT(*)                   AS item_count
FROM order_items
GROUP BY order_id;

-- GROUP_CONCAT: combine values from a group into a comma-separated string
SELECT
    category,
    GROUP_CONCAT(name ORDER BY name SEPARATOR ', ') AS product_names
FROM products
GROUP BY category;

HAVING Clause

The HAVING clause filters groups after aggregation - it's the GROUP BY equivalent of WHERE. Use WHERE to filter individual rows before grouping, and HAVING to filter groups after aggregation.

HAVING - Filter Groups After Aggregation

-- Categories with more than 2 products
SELECT category, COUNT(*) AS product_count
FROM products
GROUP BY category
HAVING product_count > 2;

-- Customers who have placed more than 1 order
SELECT customer_id, COUNT(*) AS order_count, SUM(total) AS total_spent
FROM orders
GROUP BY customer_id
HAVING order_count > 1
ORDER BY total_spent DESC;

-- WHERE vs HAVING:
-- WHERE filters rows BEFORE grouping (faster, uses indexes)
-- HAVING filters groups AFTER aggregation

-- Only count active products, then show categories with avg price > 50
SELECT category, COUNT(*) AS product_count, AVG(price) AS avg_price
FROM products
WHERE stock > 0          -- WHERE: filter rows before grouping
GROUP BY category
HAVING avg_price > 30    -- HAVING: filter groups after aggregation
ORDER BY avg_price DESC;

GROUP BY Multiple Columns and WITH ROLLUP

Multiple Columns and WITH ROLLUP

-- Group by multiple columns: orders per customer per status
SELECT customer_id, status, COUNT(*) AS order_count
FROM orders
GROUP BY customer_id, status
ORDER BY customer_id, status;

-- WITH ROLLUP: adds subtotal and grand total rows
-- NULL in the grouping column indicates a subtotal/grand total row
SELECT
    COALESCE(category, 'TOTAL') AS category,
    COUNT(*)                    AS product_count,
    SUM(stock)                  AS total_stock
FROM products
GROUP BY category WITH ROLLUP;

-- DISTINCT vs GROUP BY (equivalent for simple deduplication)
-- These two queries return the same result:
SELECT DISTINCT category FROM products;
SELECT category FROM products GROUP BY category;

Sales Summary Report

-- Monthly sales summary
SELECT
    YEAR(ordered_at)  AS year,
    MONTH(ordered_at) AS month,
    COUNT(*)          AS order_count,
    SUM(total)        AS monthly_revenue,
    AVG(total)        AS avg_order_value
FROM orders
WHERE status = 'delivered'
GROUP BY YEAR(ordered_at), MONTH(ordered_at)
ORDER BY year DESC, month DESC;

-- Top 5 best-selling products by quantity
SELECT
    p.name,
    SUM(oi.quantity)              AS total_sold,
    SUM(oi.quantity * oi.unit_price) AS total_revenue
FROM order_items oi
INNER JOIN products p ON oi.product_id = p.product_id
GROUP BY oi.product_id, p.name
ORDER BY total_sold DESC
LIMIT 5;

Applied guide for MySQL

Use MySQL when the program needs a clear answer to a specific problem, not because the keyword looks familiar. In a real MySQL task, first name the input, then name the transformation, then name the output. This small discipline shows whether the topic is being used correctly or only copied from an example.

A reliable practice flow is: create the smallest working SQL statement, add one normal case, add one edge case such as missing, repeated, empty, or boundary input, and then confirm the result with result set and EXPLAIN output. If the result surprises you, reduce the code until the behavior is visible again.

The most common trap here is copying the syntax before understanding the behavior. Avoid it by writing one sentence before the code that explains why MySQL is the right choice. After the code runs, verify the lesson by doing this: change one input and explain the changed output.

Identify the exact problem solved by MySQL.
Trace table rows, constraints, and indexes before and after the main operation.
Keep one intentionally broken version and explain the fix.
Connect the example to an order-management database so the idea feels concrete.

Key Takeaways

I can explain where MySQL fits inside an order-management database.
I can point to the exact table rows, constraints, and indexes affected by this topic.
I tested a normal case and an edge case involving missing, repeated, empty, or boundary input.
I verified the result with result set and EXPLAIN output instead of assuming it worked.
I can describe the main mistake: copying the syntax before understanding the behavior.

Common Mistakes to Avoid

WRONG Copying the syntax before understanding the behavior.

RIGHT Write the expected behavior first, then make the example prove it.

A one-line expectation turns the code from copied syntax into a testable idea.

WRONG Practicing only the perfect input.

RIGHT Also test missing, repeated, empty, or boundary input before considering the lesson complete.

The edge case is where most interview follow-up questions begin.

WRONG Looking only at the final output.

RIGHT Trace table rows, constraints, and indexes through each important step.

Tracing makes debugging faster because you can see the first incorrect state.

Practice Tasks

Build one small SQL statement that demonstrates MySQL in an order-management database.
Change the example to include missing, repeated, empty, or boundary input and record the difference.
Break the example by deliberately copying the syntax before understanding the behavior, then write the corrected version.
Explain the finished example in five bullet points: input, operation, output, failure case, and verification.

Frequently Asked Questions

When should I use MySQL?

Use it when the problem matches the behavior shown in the example and when the result can be verified through result set and EXPLAIN output.

How do I avoid mistakes in MySQL?

Start with a tiny case, then test missing, repeated, empty, or boundary input. The main warning sign is copying the syntax before understanding the behavior.

How can I revise MySQL quickly?

Trace table rows, constraints, and indexes, predict the result, run the example, and compare your prediction with the actual output.

Previous Next

MySQL GROUP BY Aggregate Functions: Tutorial, Examples, FAQs & Interview Tips