What is the difference between GROUP BY and DISTINCT?

DISTINCT only removes duplicate rows; GROUP BY forms groups and lets you aggregate (SUM, COUNT and so on) per group. If you only want a list of unique values, DISTINCT is enough; if you need one number per group, you need GROUP BY.

Are COUNT(*) and COUNT(column) the same?

No. COUNT(*) counts every row in the group; COUNT(column) counts only the non-NULL values of that column. On a column that contains NULLs, the two give different results.

What happens to NULL values when aggregating?

Functions like SUM, AVG and COUNT(column) ignore NULLs. This matters especially for AVG: NULLs are not counted as zero, they are left out entirely. If you want them counted as zero, wrap the column in COALESCE to turn NULL into 0.

Can I sort groups by their aggregate result?

Yes. Use the aggregate function, or the alias you gave it, in ORDER BY; for example ORDER BY SUM(amount) DESC puts the best-selling group on top.

← All postsThe PerSight Team · Last updated: July 23, 2026 · 7 min read

SQL GROUP BY: Making Sense of Grouping and Aggregation

Monthly revenue, orders per customer, units by category: aggregation with GROUP BY. COUNT/SUM/AVG, the difference between WHERE and HAVING, and MySQL's ONLY_FULL_GROUP_BY trap, with real examples.

Questions like "how much did we sell each month?" or "how many orders per customer?" are not about individual rows but about groups of rows. In SQL, the way to bundle rows by a column and produce a single number per bundle is GROUP BY. Once the idea clicks, half of your reports fall out of it.

What exactly does GROUP BY do?

GROUP BY collapses rows that share a value into a single group, and the aggregate function in your SELECT (COUNT, SUM, AVG, MIN, MAX) produces one result per group. Let's see counts by category:

SELECT category,
       COUNT(*) AS product_count,
       AVG(price) AS avg_price
FROM products
GROUP BY category
ORDER BY product_count DESC;

The golden rule: every column in the SELECT must either appear in GROUP BY or sit inside an aggregate function. Otherwise the database has no idea which value from the group to print.

What is the difference between WHERE and HAVING?

Both filter, but at different moments. WHERE removes rows before grouping; HAVING removes groups after grouping. A condition like "customers with at least five orders" can only be known after grouping, so it belongs in HAVING:

SELECT customer_id, COUNT(*) AS order_count
FROM orders
WHERE order_date >= DATE '2026-01-01'
GROUP BY customer_id
HAVING COUNT(*) >= 5;

SQL Server does not accept the DATE literal; write the date as a plain string (2026-01-01) there.

Watch the order: WHERE first (drop orders before 2026), then GROUP BY (bundle by customer), then HAVING (drop anyone with fewer than five). Knowing that order clears up most "why is my result wrong?" moments.

Grouping by more than one column

You can give GROUP BY several columns; groups then form on the combination of them. Revenue by year and month is the classic:

SELECT EXTRACT(YEAR FROM order_date)  AS year,
       EXTRACT(MONTH FROM order_date) AS month,
       SUM(amount) AS revenue
FROM orders
GROUP BY EXTRACT(YEAR FROM order_date), EXTRACT(MONTH FROM order_date)
ORDER BY year, month;

PostgreSQL / Oracle. On SQL Server use YEAR(order_date), MONTH(order_date); MySQL is the same.

What should I watch for by database?

The usual source of confusion is MySQL: with ONLY_FULL_GROUP_BY disabled, MySQL will happily print a column that is not in GROUP BY and return an arbitrary value for it. Silent and dangerous. Modern MySQL ships with the mode on; keep it on. PostgreSQL, SQL Server, and Oracle raise an error in that situation, which protects you from the wrong answer.

Frequently asked questions

What is the difference between GROUP BY and DISTINCT?: DISTINCT only removes duplicate rows; GROUP BY forms groups and lets you aggregate (SUM, COUNT and so on) per group. If you only want a list of unique values, DISTINCT is enough; if you need one number per group, you need GROUP BY.
Are COUNT(*) and COUNT(column) the same?: No. COUNT(*) counts every row in the group; COUNT(column) counts only the non-NULL values of that column. On a column that contains NULLs, the two give different results.
What happens to NULL values when aggregating?: Functions like SUM, AVG and COUNT(column) ignore NULLs. This matters especially for AVG: NULLs are not counted as zero, they are left out entirely. If you want them counted as zero, wrap the column in COALESCE to turn NULL into 0.
Can I sort groups by their aggregate result?: Yes. Use the aggregate function, or the alias you gave it, in ORDER BY; for example ORDER BY SUM(amount) DESC puts the best-selling group on top.

Want the same report without writing SQL? Tell PerSight "show 2026 revenue by month" or "bring me customers with at least five orders." It lays the answer out as a table and a chart, and shows you the GROUP BY query behind it.