QuerydrillGROUP BY
GROUP BY turns many rows into one row per distinct value of the grouping column(s) and lets aggregates summarize each group: SELECT country, COUNT(*) FROM customers GROUP BY country gives one row per country with its customer count. Every column in SELECT must either be in GROUP BY or be inside an aggregate.
Syntax
SELECT group_column, AGG(column) FROM table [WHERE ...] GROUP BY group_column [HAVING ...] [ORDER BY ...];| Part | What it is |
|---|---|
group_column(s) | One or more columns or expressions that define the groups |
AGG | COUNT, SUM, AVG, MIN, MAX or another aggregate applied per group |
Examples
SELECT country, COUNT(*) AS customers FROM customers GROUP BY country ORDER BY customers DESC;Result: country | customers Aruba | 38 Curacao | 25
SELECT dept, AVG(salary) AS avg_salary, MAX(salary) AS top FROM employees GROUP BY dept;Result: One row per department with its average and highest salary
SELECT customer_id, status, SUM(amount) AS total FROM orders GROUP BY customer_id, status;Result: One row per customer and status combination
Grouping by two columns gives one row per unique pair.
Common mistakes
- Selecting a column that is not grouped or aggregated: PostgreSQL raises must appear in the GROUP BY clause; SQLite silently returns a value from an arbitrary row of the group.
- Filtering with an aggregate in WHERE (WHERE COUNT(*) > 1) is an error; put it in HAVING.
- Writing GROUP BY after ORDER BY is a syntax error; the order is WHERE, GROUP BY, HAVING, ORDER BY, LIMIT.
Practice GROUP BY
These drills check your answer on live data, free, no account needed.
- Customers per countryFor each country, return the country and how many customers it has.
- Average salary per departmentFor each dept_id, return the dept_id and the average salary. Leave out employees without a department.
- Orders per statusReturn each order status and how many orders have it.
Questions
What is the difference between GROUP BY and ORDER BY?
GROUP BY merges rows into groups for aggregation; ORDER BY only sorts the output. You can use both: group first, then sort the groups.
Can I GROUP BY an alias?
SQLite and PostgreSQL both allow GROUP BY an alias defined in the SELECT list (GROUP BY month). Standard SQL and some databases do not, so repeating the expression is the portable option.
Related
- HAVINGHAVING filters the groups made by GROUP BY using aggregate conditions such as COUNT(*) > 1 or SUM(amount) > 1000. Examples, HAVING vs WHERE, both together.
- COUNTCOUNT(*) counts rows, COUNT(column) counts non-NULL values, COUNT(DISTINCT column) counts unique values. Examples with WHERE, GROUP BY and conditional counts.
- SUM, AVG, MIN, MAXThe core aggregates: SUM adds values, AVG averages them, MIN and MAX find the smallest and largest. NULL handling, integer division in AVG, use with GROUP BY.
- COUNT(DISTINCT)COUNT(DISTINCT column) counts how many different values a column holds, ignoring NULL. Unique customers, distinct values per group, distinct pairs of columns.