GROUP BY and Aggregate Functions

Aggregate functions collapse multiple rows into a single summary value: COUNT, SUM, AVG, MIN, MAX. Used alone, they summarize an entire result set:

SELECT COUNT(*) FROM orders;              -- total number of orders
SELECT SUM(total) FROM orders;            -- total revenue across all orders
SELECT AVG(total) FROM orders;            -- average order value
SELECT MIN(total), MAX(total) FROM orders; -- cheapest and priciest order

GROUP BY: aggregating per group instead of the whole table

GROUP BY splits rows into groups sharing the same value in a column, and computes the aggregate separately for each group:

SELECT customer_id, COUNT(*) AS order_count, SUM(total) AS total_spent
FROM orders
GROUP BY customer_id;

Read this as: "group all orders by customer_id, then for each group, count the rows and sum the totals." Every distinct customer_id produces exactly one row in the result, no matter how many orders that customer actually placed.

The rule: non-aggregated columns must appear in GROUP BY

This is the rule that trips people up most:

-- Error in most databases (MySQL allows it but the result is arbitrary)
SELECT customer_id, order_date, SUM(total)
FROM orders
GROUP BY customer_id;

order_date isn't wrapped in an aggregate function and isn't listed in GROUP BY, so the database has no defined way to pick a single order_date to show for a group that might span many different dates. Every column in SELECT must either be wrapped in an aggregate function, or listed in GROUP BY — there's no other well-defined value it could show.

-- Correct — order_date is part of what defines each group
SELECT customer_id, order_date, SUM(total)
FROM orders
GROUP BY customer_id, order_date;

COUNT(*) vs COUNT(column)

COUNT(*) counts rows, full stop. COUNT(column) counts only rows where that specific column is not NULL — a distinction worth knowing, since the two can give different answers on the same table:

SELECT COUNT(*) FROM orders;          -- every row
SELECT COUNT(discount_code) FROM orders; -- only orders that used a discount code

Grouping by multiple columns

Just like ORDER BY, GROUP BY accepts multiple columns, creating one group per unique combination of values across all of them — as in the customer_id, order_date example above, where each group is a specific customer on a specific day.

GROUP BY handles per-group aggregation, but what if you need to filter groups based on their aggregate result — customers with more than 5 orders, say? That's what the next lesson, on HAVING, is for.