GROUP BY collects rows that share a value into groups, so aggregate functions like COUNT, SUM and AVG return one result per group instead of one for the whole table. The examples work in PostgreSQL, MySQL, SQLite and SQL Server.
How to use GROUP BY
Name the column to group by after GROUP BY, and wrap every other column you select in an aggregate function: COUNT, SUM, AVG, MIN or MAX. You get one row for each distinct value:
SELECT group_column, AGGREGATE(value_column)
FROM table_name
GROUP BY group_column;
-- Input: scores
-- player | team | points
-- -------+------+-------
-- Ana | Red | 12
-- Ben | Blue | 9
-- Cleo | Red | 7
-- Dev | Blue | 15
-- Eli | Red | 4
SELECT team, SUM(points) AS total_points, COUNT(*) AS players
FROM scores
GROUP BY team;
-- Output:
-- team | total_points | players
-- -----+--------------+--------
-- Red | 23 | 3
-- Blue | 24 | 2
Five rows become two, one per team, and each aggregate only sees that team’s rows.
Rules to know
- Every selected column must be in GROUP BY or inside an aggregate. Otherwise there’s no single value to show for the group, and the query fails:
-- Input: products
-- name | category
-- --------+---------
-- Blender | Kitchen
-- Toaster | Kitchen
-- Pillow | Bedroom
SELECT name, category, COUNT(*) AS product_count
FROM products
GROUP BY category;
-- ERROR: column "products.name" must appear in the GROUP BY clause
-- or be used in an aggregate function
-- (SQLite runs it anyway and takes name from one row in each group)
Fix it by adding the column to GROUP BY, or by aggregating it too, with MIN(name) for example.
- NULLs form one group. Every row with a NULL in the grouping column lands in the same group. Use COALESCE to give it a label:
-- Input: products
-- name | category
-- ------------+-----------
-- Notebook | Stationery
-- Gift Card | NULL
-- Pen Set | Stationery
-- Mystery Box | NULL
-- Stapler | Stationery
SELECT category, COUNT(*) AS product_count
FROM products
GROUP BY category;
-- Output:
-- category | product_count
-- -----------+--------------
-- NULL | 2
-- Stationery | 3
SELECT COALESCE(category, 'Uncategorized') AS category, COUNT(*) AS product_count
FROM products
GROUP BY category;
-- Output:
-- category | product_count
-- --------------+--------------
-- Uncategorized | 2
-- Stationery | 3
-
WHERE filters rows before grouping, HAVING filters groups after. Only HAVING can test an aggregate such as
COUNT(*) > 2. Anything that doesn’t need an aggregate belongs in WHERE, so fewer rows get grouped. - Groups come back in no particular order. SQLite and SQL Server, for example, list Blue before Red for the scores query above. Add ORDER BY whenever the order matters.
Alternative syntax
You can group by a column alias, or by the column’s position in the SELECT list, instead of repeating an expression:
-- Input: orders
-- order_id | amount
-- ---------+-------
-- 1 | 35.00
-- 2 | 240.00
-- 3 | 18.50
-- 4 | 120.00
-- 5 | 64.00
SELECT CASE WHEN amount >= 100 THEN 'large' ELSE 'small' END AS order_size,
COUNT(*) AS order_count
FROM orders
GROUP BY order_size -- or GROUP BY 1
ORDER BY order_size;
-- SQL Server allows neither: repeat the CASE expression in GROUP BY
-- Output:
-- order_size | order_count
-- -----------+------------
-- large | 2
-- small | 3
ROLLUP adds subtotals and a grand total. The subtotal rows show NULL in the columns they add up:
-- Input: sales
-- region | category | amount
-- -------+----------+-------
-- East | Books | 100.00
-- East | Toys | 50.00
-- West | Books | 80.00
-- East | Books | 40.00
SELECT region, category, SUM(amount) AS total_sales
FROM sales
GROUP BY ROLLUP(region, category);
-- MySQL: GROUP BY region, category WITH ROLLUP
-- SQLite has no ROLLUP
-- Output:
-- region | category | total_sales
-- -------+----------+------------
-- East | Books | 140.00
-- East | Toys | 50.00
-- East | NULL | 190.00
-- West | Books | 80.00
-- West | NULL | 80.00
-- NULL | NULL | 270.00
A GROUP BY with no aggregate returns each distinct value once, the same as SELECT DISTINCT category FROM products.
When should you use GROUP BY?
Use GROUP BY whenever a question says “per” or “for each”: orders per customer, sales per month, employees per department. Most uses fall into one of these four.
Count rows in each group
How many orders have each status, how many products are in each category, how many people signed up each day. COUNT(*) counts every row in the group, and COUNT(column) counts only the rows where that column isn’t NULL.
Total, average or range for each group
SUM, AVG, MIN and MAX turn each group’s values into one number: revenue per customer, average order value, the cheapest and dearest product in a category. One query can return as many of them as you need.
Break results down by more than one column
List several columns after GROUP BY to get one row for each combination: sales per region per month, stock per warehouse per product type. Only combinations that exist in the data show up.
Keep only the groups that matter
HAVING filters the groups once they’re formed, using their aggregates: departments with more than ten people, customers who spent over $1,000, or values that appear more than once, which is how you find duplicates.
Real examples of GROUP BY
Count the products in every category, even empty ones
GROUP BY only makes groups for values that exist. To list categories with no products too, start from the categories table and LEFT JOIN the products. Count a column from the joined table rather than *: an empty category still gets one row, padded with NULLs, and COUNT(*) counts it.
-- Input: categories
-- category_name
-- -------------
-- Audio
-- Cameras
-- Drones
-- Input: products
-- id | name | category
-- ---+------------+---------
-- 1 | Earbuds | Audio
-- 2 | Soundbar | Audio
-- 3 | Action Cam | Cameras
-- Wrong: COUNT(*) counts the NULL row of an empty category
SELECT c.category_name, COUNT(*) AS product_count
FROM categories c
LEFT JOIN products p ON p.category = c.category_name
GROUP BY c.category_name
ORDER BY c.category_name;
-- Output:
-- category_name | product_count
-- --------------+--------------
-- Audio | 2
-- Cameras | 1
-- Drones | 1
-- Right: COUNT(p.id) skips it
SELECT c.category_name, COUNT(p.id) AS product_count
FROM categories c
LEFT JOIN products p ON p.category = c.category_name
GROUP BY c.category_name
ORDER BY c.category_name;
-- Output:
-- category_name | product_count
-- --------------+--------------
-- Audio | 2
-- Cameras | 1
-- Drones | 0
Find the top three categories by revenue
Sum each category’s sales, sort by the total and keep the first three. Books comes second although each of its sales is smaller than the one Toys sale:
-- Input: purchases
-- category | amount
-- ---------+-------
-- Books | 45.00
-- Toys | 120.00
-- Garden | 310.00
-- Books | 80.00
-- Music | 25.00
SELECT category, SUM(amount) AS revenue
FROM purchases
GROUP BY category
ORDER BY revenue DESC
LIMIT 3;
-- SQL Server has no LIMIT: write SELECT TOP 3 category, ... instead
-- Output:
-- category | revenue
-- ---------+--------
-- Garden | 310.00
-- Books | 125.00
-- Toys | 120.00
Report sales per region per month
Group by the region and by the month taken from the date, so each region gets one row per month:
-- Input: sales
-- region | sale_date | amount
-- -------+------------+-------
-- East | 2024-01-08 | 120.00
-- West | 2024-01-15 | 80.00
-- East | 2024-01-22 | 95.00
-- East | 2024-02-03 | 60.00
-- West | 2024-02-11 | 150.00
SELECT region, EXTRACT(MONTH FROM sale_date) AS month, SUM(amount) AS total_sales
FROM sales
GROUP BY region, EXTRACT(MONTH FROM sale_date)
ORDER BY region, month;
-- Output:
-- region | month | total_sales
-- -------+-------+------------
-- East | 1 | 215.00
-- East | 2 | 60.00
-- West | 1 | 80.00
-- West | 2 | 150.00
SQL Server and SQLite don’t have EXTRACT. Use MONTH(sale_date) in SQL Server and strftime('%m', sale_date) in SQLite. Group by the year as well once your data covers more than one.
Find repeat customers this year
WHERE keeps only the 2024 orders, then HAVING keeps the customers with at least two of them. Customer 202 has two orders in total but only one in 2024, so it’s left out:
-- Input: orders
-- order_id | customer_id | order_date
-- ---------+-------------+-----------
-- 1 | 201 | 2023-11-20
-- 2 | 202 | 2023-12-05
-- 3 | 201 | 2024-01-09
-- 4 | 202 | 2024-02-14
-- 5 | 201 | 2024-03-02
SELECT customer_id, COUNT(*) AS orders_2024
FROM orders
WHERE order_date >= '2024-01-01'
GROUP BY customer_id
HAVING COUNT(*) >= 2;
-- Output:
-- customer_id | orders_2024
-- ------------+------------
-- 201 | 2
Further reading about GROUP BY
On this blog:
- SQL COUNT DISTINCT guide: counting unique values in each group
-
SQL find duplicates guide: GROUP BY with
HAVING COUNT(*) > 1 - SQL ORDER BY guide: sorting the grouped results
- SQL CASE statement guide: conditional counts and sums inside an aggregate
- SQL pivot table guide: turning grouped rows into columns
- SQL COALESCE guide: labelling the NULL group
- SQL JOIN diagrams guide: the LEFT JOIN that keeps empty groups
Official documentation:
- PostgreSQL: GROUP BY and HAVING
- MySQL: Handling of GROUP BY
- SQLite: SELECT (aggregate queries)
- SQL Server: GROUP BY
Beekeeper Studio Is A Free & Open Source Database GUI
Best SQL query & editor tool I have ever used. It provides everything I need to manage my database. - ⭐⭐⭐⭐⭐ Mit
Beekeeper Studio is fast, intuitive, and easy to use. Beekeeper supports loads of databases, and works great on Windows, Mac and Linux.
What Users Say About Beekeeper Studio
"Beekeeper Studio completely replaced my old SQL workflow. It's fast, intuitive, and makes database work enjoyable again."
"I've tried many database GUIs, but Beekeeper strikes the perfect balance between features and simplicity. It just works."