Aggregates
An aggregate function turns many values into one. COUNT(*) counts rows; SUM(price) adds up a column. Over all 7 café orders, COUNT(*) is 7 and SUM(price) is 27.
GROUP BY, step by step
One total is not enough: the café wants cups and takings for each drink. GROUP BY asks for that. Read the query: per drink, count the orders and add up the prices, keeping drinks that took more than 5.
As always, the database starts with FROM: the orders table, all 7 rows. This query has no WHERE, so no row is dropped before grouping.
GROUP BY drink puts rows with the same drink into one group. Rows 1, 3 and 6 go to latte, 2 and 5 to tea, 4 and 7 to mocha. Every row lands in exactly one group.
Now COUNT(*) and SUM(price) run once per group. latte: 3 orders, 13 taken. tea: 2 and 5. mocha: 2 and 9. Each group collapses into one row.
HAVING tests each whole group, after the aggregates. Is SUM(price) greater than 5? latte (13) and mocha (9) pass. tea took exactly 5, which is not more than 5, so the tea group is dropped.
SELECT builds one output row per kept group: the drink, its count and its sum. ORDER BY drink sorts them. The answer: latte 3 13 and mocha 2 9.
The rules
After grouping, each group gives one row. So SELECT may list only grouped columns and aggregates. Ask for plain price and PostgreSQL refuses: the latte group holds 4, 4 and 5. Which one?
SQL is written SELECT first but run in this order: FROM, WHERE, GROUP BY, HAVING, SELECT, ORDER BY. So WHERE filters single rows before grouping and cannot use SUM; HAVING filters whole groups after.
What happens
;; step 1/9 · aggregate aggregate function many values in, one value out COUNT(*) how many rows SUM(price) add up the prices AVG, MIN and MAX work the same way id drink price 1 latte 4 2 tea 2 3 latte 4 4 mocha 5 5 tea 3 6 latte 5 7 mocha 4 SELECT COUNT(*), SUM(price) FROM orders; count 7 sum 27 4 + 2 + 4 + 5 + 3 + 5 + 4 = 27
Without ORDER BY, SQL does not promise any order for the groups in an answer. The figure lines up the groups in the order their first row appears. COUNT(*) counts rows. COUNT(price) and SUM(price) skip empty (NULL) prices. Over no rows at all, COUNT gives 0 but SUM, AVG, MIN and MAX give NULL.