SQL GROUP BY Multiple Columns: Examples and Practice

Grouping by one column gives one row per value:

SELECT category, COUNT(*) AS products
FROM products
GROUP BY category
ORDER BY category
LIMIT 4;

▶ Run this query in the SQL Playground

Add a second column and every combination becomes its own group. Products per category and status:

SELECT category, status, COUNT(*) AS products
FROM products
GROUP BY category, status
ORDER BY category, status
LIMIT 8;

▶ Run this query in the SQL Playground

Only combinations that occur in the data appear. A category with no discontinued products has no "discontinued" row; it is not shown with a 0.

SELECT col_a, col_b, AGGREGATE(col_c)
FROM table
GROUP BY col_a, col_b;

Orders per year and status

SELECT strftime('%Y', order_date) AS year,
       status,
       COUNT(*) AS orders,
       ROUND(SUM(total), 2) AS revenue
FROM orders
GROUP BY year, status
ORDER BY year, orders DESC
LIMIT 8;

▶ Run this query in the SQL Playground

Employees per department and city

SELECT department, city, COUNT(*) AS employees
FROM employees
WHERE department = 'Engineering'
GROUP BY department, city
ORDER BY employees DESC, city;

▶ Run this query in the SQL Playground

Three columns

More columns mean smaller groups. Payments per method, status and year:

SELECT method, status, strftime('%Y', paid_at) AS year, COUNT(*) AS payments
FROM payments
WHERE method = 'Credit Card'
GROUP BY method, status, year
ORDER BY year, status;

▶ Run this query in the SQL Playground

Each group can carry as many aggregates as you need. A salary overview per department:

SELECT department,
       COUNT(*)            AS employees,
       MIN(salary)         AS lowest,
       MAX(salary)         AS highest,
       ROUND(AVG(salary))  AS average,
       SUM(salary)         AS payroll
FROM employees
GROUP BY department
ORDER BY payroll DESC;

▶ Run this query in the SQL Playground

COUNT(DISTINCT) per group

COUNT(DISTINCT col) counts different values inside each group. Orders and distinct customers per shipping city:

SELECT shipping_city,
       COUNT(*) AS orders,
       COUNT(DISTINCT user_id) AS customers
FROM orders
GROUP BY shipping_city
ORDER BY orders DESC
LIMIT 6;

▶ Run this query in the SQL Playground

When orders is larger than customers, some customers ordered more than once to that city.

Two rules. First, GROUP BY puts all NULLs in one group. Second, aggregates other than COUNT(*) skip NULLs. Both show up when grouping orders by whether they shipped:

SELECT status,
       COUNT(*) AS orders,
       COUNT(shipped_date) AS with_shipped_date,
       MIN(shipped_date) AS first_shipment
FROM orders
GROUP BY status
ORDER BY orders DESC;

▶ Run this query in the SQL Playground

For pending, processing, cancelled and refunded orders, COUNT(shipped_date) is 0 and MIN is NULL: there are no values to work with. Grouping by a column that contains NULLs gives one extra group for them:

SELECT manager_id, COUNT(*) AS employees
FROM employees
GROUP BY manager_id
ORDER BY manager_id
LIMIT 4;

▶ Run this query in the SQL Playground

The first row, with NULL, holds the 10 employees who have no manager. AVG over a group also ignores NULLs, so it averages only the values that exist; wrap the column in COALESCE if missing values should count as 0.

HAVING filters the groups after aggregation. Category and status combinations with more than 10 products:

SELECT category, status, COUNT(*) AS products
FROM products
GROUP BY category, status
HAVING COUNT(*) > 10
ORDER BY products DESC;

▶ Run this query in the SQL Playground

Use WHERE for conditions on single rows (they run first and shrink the groups) and HAVING for conditions on aggregates. See HAVING.

Reports often want a total per group plus a grand total. PostgreSQL, SQL Server, Oracle and MySQL have ROLLUP for this:

-- PostgreSQL, SQL Server, Oracle (MySQL: GROUP BY status WITH ROLLUP)
SELECT status, COUNT(*) AS orders
FROM orders
GROUP BY ROLLUP (status);

SQLite has no ROLLUP, but UNION ALL gives the same result:

SELECT status, COUNT(*) AS orders
FROM orders
GROUP BY status
UNION ALL
SELECT 'All orders', COUNT(*)
FROM orders
ORDER BY orders;

▶ Run this query in the SQL Playground

With two columns, ROLLUP (year, status) adds a subtotal per year and a grand total; CUBE adds subtotals for every combination; GROUPING SETS lets you list exactly which groupings you want.

Five errors and how to avoid them.

SELECT category, status, COUNT(*)
FROM products
GROUP BY category;

SELECT category, status, COUNT(*)
FROM products
GROUP BY category, status;

SELECT department, city, title, COUNT(*)
FROM employees
GROUP BY department, city, title;

SELECT department, COUNT(*)
FROM employees
GROUP BY department;

GROUP BY strftime('%m', order_date)

GROUP BY strftime('%Y', order_date), strftime('%m', order_date)

SELECT status, COUNT(shipped_date) AS orders
FROM orders GROUP BY status;

SELECT status, COUNT(*) AS orders
FROM orders GROUP BY status;

SELECT category, COUNT(*)
FROM products
WHERE COUNT(*) > 10
GROUP BY category;

SELECT category, COUNT(*)
FROM products
GROUP BY category
HAVING COUNT(*) > 10;

5 exercises on the real Playground tables.

Write each query yourself first. Every solution has been run against the Playground database.

1. 1Easy Count users per country and premium status. Table: users

   Show solution

   SQLite · runs in the Playground

   SELECT country, is_premium, COUNT(*) AS users
   FROM users
   GROUP BY country, is_premium
   ORDER BY country, is_premium;

   ▶ Run this query in the SQL Playground

   Each country appears once per premium value that exists for it.

2. 2Easy For each rating, show the number of reviews and the average helpful votes. Table: reviews

   Show solution

   SQLite · runs in the Playground

   SELECT rating, COUNT(*) AS reviews, ROUND(AVG(helpful_votes), 1) AS avg_votes
   FROM reviews
   GROUP BY rating
   ORDER BY rating DESC;

   ▶ Run this query in the SQL Playground

   Two aggregates per group.

3. 3Medium Show revenue per year and month for 2024, excluding cancelled and refunded orders. Table: orders

   Show solution

   SQLite · runs in the Playground

   SELECT strftime('%Y', order_date) AS year,
          strftime('%m', order_date) AS month,
          ROUND(SUM(total), 2) AS revenue
   FROM orders
   WHERE order_date >= '2024-01-01'
     AND status NOT IN ('cancelled', 'refunded')
   GROUP BY year, month
   ORDER BY year, month;

   ▶ Run this query in the SQL Playground

   WHERE removes rows first; the year column keeps the months of different years apart.

4. 4Medium Show units sold and the number of different products sold per category. Table: order_items, products

   Show solution

   SQLite · runs in the Playground

   SELECT p.category,
          SUM(oi.quantity) AS units,
          COUNT(DISTINCT oi.product_id) AS different_products
   FROM order_items oi
   JOIN products p ON p.id = oi.product_id
   GROUP BY p.category
   ORDER BY units DESC;

   ▶ Run this query in the SQL Playground

   COUNT(DISTINCT) counts each product once per category even if it was ordered many times.

5. 5Hard List the department and city combinations with at least 3 employees, with their average salary. Table: employees

   Show solution

   SQLite · runs in the Playground

   SELECT department, city,
          COUNT(*) AS employees,
          ROUND(AVG(salary)) AS avg_salary
   FROM employees
   GROUP BY department, city
   HAVING COUNT(*) >= 3
   ORDER BY employees DESC, avg_salary DESC;

   ▶ Run this query in the SQL Playground

   HAVING keeps only the larger combinations.