GROUP BY & HAVING: turning many rows into one summary row

Sometimes you don't want individual rows, you want a summary. "How much has each customer spent in total?" GROUP BY is how you roll many rows up into one summary row per group.

🎯 Explain Like I'm Hired Imagine a shoebox full of receipts. GROUP BY customer is sorting them into one pile per customer, then writing a single summary line for each pile ("Riya: 4 orders, ₹3,200 total"). HAVING is a filter you apply to those summary lines after the piles are made ("only show piles over ₹1,000"). That's different from WHERE, which filters individual receipts before they're sorted into piles. Example: "which customers spent over ₹1,000?" needs HAVING, because "over ₹1,000" is a total that only exists after you've added up each customer's orders, you can't check it on a single row.

SELECT
  customer_id,
  COUNT(*)     AS order_count,    -- how many orders
  SUM(amount)  AS total_spent     -- total money
FROM orders
WHERE status = 'delivered'        -- filter rows FIRST
GROUP BY customer_id              -- one row per customer
HAVING SUM(amount) > 1000         -- filter the summaries
ORDER BY total_spent DESC;

COUNT, SUM, AVG, MIN, MAX are called aggregate functions: they take many values and return one (a count, a total, an average). Any column you SELECT that isn't wrapped in one of these has to appear in the GROUP BY, so the database knows how to form the groups.

WHERE filters rows, HAVING filters groups

The one idea to remember: WHERE runs before grouping (on individual rows), HAVING runs after grouping (on the summary numbers). The order the database actually does things:

1. grab the rows (FROM)
2. filter rows (WHERE)
3. roll them into groups (GROUP BY)
4. filter the groups (HAVING)
5. sort (ORDER BY)

So filter as much as you can in WHERE, since it means fewer rows to add up. Save HAVING for conditions on the totals, which don't exist until after grouping.

The interview trap

-- Looks fine, but is meaningless
SELECT customer_id, SUM(amount) FROM orders;

You asked for a total (SUM) but also a plain customer_id, with no GROUP BY. Postgres will just error. The rule: the moment you use an aggregate, every other column in your SELECT needs to be in the GROUP BY.

---

Next: The five JOIN types →