GROUP BY and HAVING

The idea

"What's our revenue?" is one number. "What's our revenue per category?" is a small table: one number for each category. That's the most common shape of a business question, and GROUP BY answers it.

Your first GROUP BY

Start with a simple version of the question: how many products does the shop sell in each category? You could run five separate queries, one WHERE category = ... per category, but you'd have to know all the categories in advance, and a new one would be missed. GROUP BY works them out for you:

SELECT category, COUNT(*) AS products
FROM products
GROUP BY category
ORDER BY category;

 category    | products
-------------+----------
 Coffee      |        3
 Electronics |        3
 Home        |        3
 Kitchen     |        3
 Stationery  |        3
(5 rows)

Here's what happened:

1. FROM products opened all 15 rows.
2. GROUP BY category made one pile per category.
3. SELECT category, COUNT(*) produced one row per pile: the pile's category and how many rows it had.

Any aggregate works per group:

SELECT department,
       COUNT(*) AS headcount,
       ROUND(AVG(salary)) AS avg_salary,
       MAX(salary) AS top_salary
FROM employees
GROUP BY department
ORDER BY avg_salary DESC;

 department | headcount | avg_salary | top_salary
------------+-----------+------------+------------
 Management |         1 |   45000000 |   45000000
 Data       |         2 |   12750000 |   13000000
 Sales      |         5 |   11800000 |   22000000
 Operations |         4 |   10625000 |   21000000
(4 rows)

The golden rule

Remember the error from the last module, SELECT name, MAX(price) FROM products? With GROUP BY, the rule becomes precise:

category is fine (one value per category pile). COUNT(*) is fine (one count per pile). name alone isn't: a pile has several names.

Grouping by several columns

Sometimes one way of splitting the data isn't enough. The operations team wants to know whether cancellations happen more on the website or in the app. That needs orders split by status and by channel at the same time. Group by two columns and you get one row per combination of the two:

SELECT status, channel, COUNT(*) AS orders
FROM orders
GROUP BY status, channel
ORDER BY status, channel;

 status    | channel | orders
-----------+---------+--------
 cancelled | app     |      2
 cancelled | web     |      8
 completed | app     |     10
 completed | web     |     16
 pending   | web     |      1
 shipped   | web     |      3
(6 rows)

Totals per order

Grouping is also how you get from detail rows to totals. An order for a coffee, a mug and a kettle is stored as three lines in order_items, one per product, and no table holds the order's total. Group the lines by order_id, and SUM adds up each order's lines into its total:

SELECT order_id,
       COUNT(*) AS lines,
       SUM(quantity * unit_price) AS order_total
FROM order_items
GROUP BY order_id
ORDER BY order_total DESC
LIMIT 5;

 order_id | lines | order_total
----------+-------+-------------
       32 |     3 |     1070000
       17 |     2 |     1024000
        3 |     1 |      960000
        8 |     3 |      918000
       26 |     1 |      867000
(5 rows)

HAVING: filtering groups

Which orders were worth over Rp 1.000.000? You can't use WHERE for that: WHERE runs before grouping, when the total doesn't exist yet. Try it and PostgreSQL stops you:

SELECT order_id, SUM(quantity * unit_price) AS order_total
FROM order_items
WHERE SUM(quantity * unit_price) > 1000000
GROUP BY order_id;

ERROR: aggregate functions are not allowed in WHERE

HAVING is the filter that runs after grouping, on the groups:

SELECT order_id, SUM(quantity * unit_price) AS order_total
FROM order_items
GROUP BY order_id
HAVING SUM(quantity * unit_price) > 1000000
ORDER BY order_total DESC;

 order_id | order_total
----------+-------------
       32 |     1070000
       17 |     1024000
(2 rows)

WHERE and HAVING together

They do different jobs, and you'll often use both:

• WHERE filters rows before grouping: "only completed orders".
• HAVING filters groups after grouping: "only customers with at least 3 of them".

SELECT customer_id, COUNT(*) AS completed_orders
FROM orders
WHERE status = 'completed'
GROUP BY customer_id
HAVING COUNT(*) >= 3
ORDER BY completed_orders DESC, customer_id;

 customer_id | completed_orders
-------------+------------------
          11 |                6
          12 |                5
           8 |                4
           5 |                3
(4 rows)

The full run order is now:

Why this matters for clean code

Most dashboards are a stack of GROUP BY queries. The habits that keep them correct: group by exactly the columns you show, filter rows in WHERE and groups in HAVING, and name every aggregate. When a number looks wrong, ask "what is one row of my result?" ("one row per category", "one row per order"). If you can't answer in a sentence, the query needs another look.

Recap

• GROUP BY col makes one output row per distinct value (or combination of values).
• Every SELECT column must be grouped or aggregated.
• WHERE filters rows before grouping; HAVING filters groups after.
• Aggregates can't go in WHERE.
• Run order: FROM → WHERE → GROUP BY → HAVING → SELECT → ORDER BY → LIMIT.