GROUP BY

GROUP BY

What it means

GROUP BY collapses rows sharing the same value(s) in specified column(s) into a single summary row per group, typically paired with aggregate functions (COUNT, SUM, AVG, etc.). HAVING filters after grouping (on aggregated values), while WHERE filters before grouping (on individual rows).

Examples

SELECT CustomerId, COUNT(*) AS order_count, SUM(Amount) AS total_spent
FROM Orders
GROUP BY CustomerId;

-- Group by multiple columns
SELECT CustomerId, YEAR(OrderDate) AS order_year, SUM(Amount) AS yearly_total
FROM Orders
GROUP BY CustomerId, YEAR(OrderDate);

-- Filter groups with HAVING (after aggregation)
SELECT CustomerId, SUM(Amount) AS total_spent
FROM Orders
GROUP BY CustomerId
HAVING SUM(Amount) > 1000;

-- WHERE filters rows BEFORE grouping; HAVING filters groups AFTER
SELECT CustomerId, COUNT(*) AS order_count
FROM Orders
WHERE OrderDate >= '2026-01-01'
GROUP BY CustomerId
HAVING COUNT(*) > 5;

Common mistake

Using WHERE to filter on an aggregate value — WHERE runs before grouping happens, so it has no access to aggregated results like SUM(Amount); that’s exactly what HAVING is for.

-- Error: Invalid column name / aggregate not allowed in WHERE
SELECT CustomerId, SUM(Amount)
FROM Orders
WHERE SUM(Amount) > 1000
GROUP BY CustomerId;

-- Correct: use HAVING to filter on the aggregated result
SELECT CustomerId, SUM(Amount)
FROM Orders
GROUP BY CustomerId
HAVING SUM(Amount) > 1000;

Quick practice

1. What’s the difference between WHERE and HAVING?

   Answer

   WHERE filters individual rows before grouping/aggregation happens; HAVING filters entire groups after aggregation, and can reference aggregate functions like SUM().

2. What does GROUP BY CustomerId, YEAR(OrderDate) produce?

   Answer

   One summary row per unique combination of customer and order year — not one row per customer overall.

3. Can WHERE reference an aggregate function like COUNT(*)?

   Answer

   No — aggregates aren't computed yet when WHERE runs; use HAVING instead to filter on aggregated values.