Grouping and aggregates
An aggregate function such as COUNT or SUM turns many values into one. Add GROUP BY and the database first sorts the rows into groups, then runs the aggregates once per group, so each group becomes one row of the answer. Follow a café's orders through GROUP BY drink, COUNT(*), SUM(price) and HAVING, and learn why WHERE filters rows but HAVING filters groups.
5 quick questions
Pick an answer to see why. Your score saves with your progress.
01The orders table has 7 rows with prices 4, 2, 4, 5, 3, 5 and 4. What does SELECT COUNT(*), SUM(price) FROM orders return?
Without GROUP BY an aggregate runs over the whole table and gives one row. COUNT(*) counts the 7 rows; SUM(price) adds 4 + 2 + 4 + 5 + 3 + 5 + 4 = 27.
02How many rows does SELECT drink, COUNT(*) FROM orders GROUP BY drink return for the café's orders (latte, tea and mocha)?
GROUP BY drink makes one group per distinct drink, and each group collapses into one output row: latte 3, tea 2 and mocha 2.
03You want only the drinks that took more than 5 in total. Which clause do you use?
HAVING tests whole groups after the aggregates are worked out. WHERE runs before grouping, on one row at a time, so it cannot use SUM: PostgreSQL says aggregate functions are not allowed in WHERE.
04Why does PostgreSQL refuse SELECT drink, price FROM orders GROUP BY drink?
In a grouped query, SELECT may list only grouped columns and aggregates. Say which value you want: SUM(price), MIN(price), MAX(price) or AVG(price).
05The tea group took exactly 5. With HAVING SUM(price) > 5, what happens to it?
> means strictly greater. 5 > 5 is false, so the whole tea group is dropped; latte (13) and mocha (9) are kept.
Glossary
- Aggregate function
- A function that turns many values into one, such as COUNT, SUM, AVG, MIN or MAX.
- COUNT(*)
- Counts rows. COUNT(column) counts only the rows where that column is not NULL.
- SUM
- Adds up the values of a column. It skips NULLs, and gives NULL when there are no rows at all.
- GROUP BY
- Sorts the rows into groups, one per distinct value of the listed columns; each group becomes one output row.
- Group
- All the rows that share the same value in the GROUP BY columns, such as every latte order.
- HAVING
- Keeps or drops whole groups, after the aggregates are worked out, such as HAVING SUM(price) > 5.
- WHERE
- Keeps or drops single rows before any grouping. It cannot use an aggregate.
- NULL
- SQL's marker for a missing or unknown value.
Notes and sources
- 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.
- 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.
- SQLite is more relaxed: it accepts a bare column such as price in a grouped query and takes it from one row of the group. PostgreSQL refuses, as the figure shows. Write the aggregate you mean.
- PostgreSQL names the output columns count and sum. Add AS to choose a name: COUNT(*) AS cups.
- AVG of whole numbers is not always whole: in PostgreSQL AVG(price) for latte is 4.33 (shown with many more decimal places).
- The Python example uses the sqlite3 module that ships with Python. Run it and it prints latte 3 13 and mocha 2 9, one per line.
- PostgreSQL documentation: Tutorial, Aggregate Functions
- PostgreSQL documentation: Table Expressions (GROUP BY and HAVING)
- PostgreSQL documentation: SELECT
- PostgreSQL documentation: Aggregate Functions
- SQLite: SELECT (bare columns in an aggregate query)
- SQLite: Built-in Aggregate Functions
- Python Library Reference: sqlite3
- Silberschatz, Korth and Sudarshan, Database System Concepts (7th ed.), section 3.7 Aggregate Functions
- Elmasri and Navathe, Fundamentals of Database Systems (7th ed.), section 7.1.7 Grouping: GROUP BY and HAVING
Quiz Unlocks after the last step.