GROUP BY

One row per group

The last topic gave one number for the whole table. Usually you want that number for each group: how many students in each city, the total of each order, the average salary in each department. GROUP BY column puts rows with the same value in that column into one group, then each aggregate runs separately for every group.

SELECT city, COUNT(*) AS students
FROM students
GROUP BY city;
-- Chennai   | 1
-- Delhi     | 2
-- Hyderabad | 1
-- Mumbai    | 1
-- Pune      | 3

Totals per order

order_items has one row per product in an order. To get each order's value, group the lines by order_id and add up qty × price.

SELECT order_id, SUM(qty * price) AS order_total
FROM order_items
GROUP BY order_id;
-- 101 | 1780
-- 102 | 1999
-- 103 | 2248   ... 8 orders

Several aggregates at once, and the NULL group

SELECT dept_id, COUNT(*) AS employees, ROUND(AVG(salary), 2) AS avg_salary
FROM employees
GROUP BY dept_id;
-- NULL | 1 | 45000
-- 1    | 4 | 140000
-- 2    | 3 | 80666.67
-- 3    | 2 | 86500
-- 4    | 1 | 80000

The golden rule

After grouping, each result row stands for a whole group. A column in GROUP BY has one value per group, so it can be shown. A column inside an aggregate becomes one value per group, so it can be shown. Any other column has many values in the group, and SQL cannot know which one to show. So: every selected column is either grouped or aggregated.

SELECT city, name, MAX(marks) FROM students GROUP BY city;   -- name is neither!

Grouping by more than one column

GROUP BY city, course makes one group for each different (city, course) pair. Pune with BCA, Pune with BCom and Pune with BSc are three groups. The more columns, the smaller the groups.

SELECT category, COUNT(*) AS products, MIN(price) AS cheapest, MAX(price) AS dearest
FROM products
GROUP BY category
ORDER BY category;
-- Electronics | 2 | 1299 | 1999
-- Fashion     | 2 | 899  | 2499
-- Grocery     | 2 | 160  | 650
-- Kitchen     | 2 | 450  | 1450

Ordering groups

GROUP BY does not promise any order (SQLite often returns groups sorted, but that is a side effect). Add ORDER BY. You can sort by a grouped column, by an aggregate, or by its alias: ORDER BY students DESC gives the biggest city first. With ties, add a tie-breaker such as ORDER BY students DESC, city.

Where GROUP BY sits in a query

Written order: SELECT, FROM, WHERE, GROUP BY, HAVING, ORDER BY, LIMIT. WHERE comes before GROUP BY, so it filters rows before they are put into groups. The next topic adds HAVING, which filters whole groups.

Common mistakes

• Selecting a column that is neither grouped nor aggregated. Add it to GROUP BY, or wrap it in an aggregate.
• Grouping by too many columns (for example by id), so every group has one row and nothing is summarised.
• Expecting groups to come out sorted. Always write ORDER BY.
• Forgetting the NULL group. Rows with a NULL key form their own group; filter them with WHERE col IS NOT NULL or label them with COALESCE.
• Grouping by a display name that is not unique, so two different customers called "Ravi Kumar" merge into one group. Group by the id, and show the name alongside (module 5 shows how with a JOIN).