SQL GROUP BY: Syntax, Examples & Practice

Here is the question "how many employees work in each department?" written in SQL:

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

▶ Run this query in the SQL Playground

The employees table has 120 rows. GROUP BY department sorts those rows into 10 piles — one per department — and COUNT(*) counts the rows in each pile. The result has one row per group instead of one row per employee: 120 rows in, 10 rows out.

Without GROUP BY vs With GROUP BY

GROUP BY builds on two earlier lessons: choosing columns with SELECT and the other core SQL clauses, and filtering rows with WHERE.

The pattern every grouped query follows.

SELECT   column_name,                 -- what to group by
         aggregate_function(column)   -- what to calculate per group
FROM     table_name
WHERE    condition                    -- optional: filter rows first
GROUP BY column_name;

Where GROUP BY Goes: Clause Order

SQL clauses are written in a fixed order. GROUP BY comes after FROM and WHERE, and before HAVING and ORDER BY:

SELECT department
GROUP BY department
FROM employees;

SELECT department
FROM employees
GROUP BY department;

GROUP BY makes the groups; an aggregate function turns each group into a number.

An aggregate function takes many rows and returns one value. With GROUP BY it runs once per group. These five cover almost every report — select one to jump to its example:

GROUP BY with COUNT()

COUNT(*) returns the number of rows in each group. How many orders are in each status?

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

▶ Run this query in the SQL Playground

COUNT(column) is slightly different: it skips rows where that column is NULL. Only shipped and delivered orders have a shipped_date, so the two counts disagree:

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

▶ Run this query in the SQL Playground

GROUP BY with SUM()

SUM(column) adds up a numeric column inside each group. What is the total payroll of each department?

SELECT department,
       SUM(salary) AS total_salary
FROM employees
GROUP BY department;

▶ Run this query in the SQL Playground

GROUP BY with AVG()

AVG(column) returns the mean of each group. What is the average product price in each category?

SELECT category,
       ROUND(AVG(price), 2) AS avg_price
FROM products
GROUP BY category;

▶ Run this query in the SQL Playground

ROUND(…, 2) only tidies the output to two decimals. AVG ignores NULL values rather than treating them as zero.

GROUP BY with MIN() and MAX()

MIN and MAX return the smallest and largest value in each group. One query can use several aggregates at once — what is the salary range in each department?

SELECT department,
       COUNT(*)    AS employees,
       MIN(salary) AS lowest,
       MAX(salary) AS highest
FROM employees
GROUP BY department;

▶ Run this query in the SQL Playground

MIN and MAX also work on text and dates: MIN(order_date) is each group's earliest order. The full set of functions is covered in SQL aggregate functions.

One group for every unique combination.

List more than one column and GROUP BY creates a group for each combination of their values. Grouping employees by department, status counts active and on-leave staff separately inside every department:

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

▶ Run this query in the SQL Playground

Customer Success appears twice — once for active and once for on_leave — because those are two different combinations. Engineering appears once, because nobody there is on leave: a combination with no rows produces no group. A location report works the same way: GROUP BY country, city keeps a Springfield in one country apart from a Springfield in another.

Choose which rows are allowed into the groups.

WHERE runs before grouping, so it decides which rows get counted at all. How many delivered orders went to each city?

SELECT shipping_city,
       COUNT(*) AS delivered_orders
FROM orders
WHERE status = 'delivered'
GROUP BY shipping_city
ORDER BY delivered_orders DESC
LIMIT 5;

▶ Run this query in the SQL Playground

Of 200 orders, WHERE keeps the 82 delivered ones; only those are grouped by city. Cancelled, pending and refunded orders never reach COUNT. For the operators WHERE supports, see how SQL WHERE filters rows.

WHERE filters rows before grouping. HAVING filters groups after aggregation.

Which cities received 12 or more orders? The condition is about each city's count — a number that does not exist until the groups have been built. That is what HAVING is for:

SELECT shipping_city,
       COUNT(*) AS order_count
FROM orders
GROUP BY shipping_city
HAVING COUNT(*) >= 12
ORDER BY order_count DESC;

▶ Run this query in the SQL Playground

There are 20 cities; HAVING keeps the 4 whose group has at least 12 rows.

Why WHERE Cannot Replace HAVING Here

SELECT shipping_city, COUNT(*)
FROM orders
WHERE COUNT(*) >= 12
GROUP BY shipping_city;

SELECT shipping_city, COUNT(*)
FROM orders
GROUP BY shipping_city
HAVING COUNT(*) >= 12;

WHERE looks at one row at a time, and a single row has no count. HAVING looks at one group at a time.

Using WHERE and HAVING Together

They are not alternatives — a query can use both. Among delivered orders only, which cities have 5 or more?

SELECT shipping_city,
       COUNT(*) AS delivered_orders
FROM orders
WHERE status = 'delivered'     -- 1. filter rows
GROUP BY shipping_city         -- 2. build groups
HAVING COUNT(*) >= 5           -- 3. filter groups
ORDER BY delivered_orders DESC;

▶ Run this query in the SQL Playground

GROUP BY creates the groups; ORDER BY decides the order you see them in.

Which cities bring in the most revenue? Group, total, then sort by the total:

SELECT shipping_city,
       ROUND(SUM(total), 2) AS total_revenue
FROM orders
GROUP BY shipping_city
ORDER BY total_revenue DESC
LIMIT 5;

▶ Run this query in the SQL Playground

ORDER BY can sort by the grouped column (ORDER BY shipping_city) or by an aggregate. Because it runs last, it can use the alias total_revenue defined in SELECT.

Aggregate across related tables.

Often the column you want to group by lives in a different table from the rows you want to count. Join first, then group the joined rows. How many orders were placed by customers living in each city? The city is in users; the orders are in orders:

SELECT u.city,
       COUNT(o.id) AS total_orders
FROM users u
JOIN orders o ON o.user_id = u.id
GROUP BY u.city
ORDER BY total_orders DESC
LIMIT 5;

▶ Run this query in the SQL Playground

The JOIN produces one row per order with its customer's details attached; GROUP BY then collapses those rows by u.city. Prefix columns with the table alias (u., o.) so it is clear which table each comes from.

Same rows, different jobs.

DISTINCT removes duplicate rows from the result. GROUP BY builds groups so that something can be calculated for each one. Used with no aggregate, they return the same rows:

SELECT DISTINCT department
FROM employees;

SELECT department
FROM employees
GROUP BY department;

The moment you want "…and how many of each", DISTINCT cannot help and GROUP BY can. For de-duplication on its own, see the SELECT DISTINCT lesson.

Eight errors that account for most GROUP BY trouble — and the fix for each.

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

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

SELECT department, COUNT(*)
FROM employees;

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

Eight questions an analyst gets asked, answered on the Playground's e-commerce data.

Each example gives the business question, the query, its real output and the idea to take away. All of them run in the Playground — open one, then change a column or a threshold and run it again.

SELECT u.first_name, u.last_name,
       COUNT(o.id)            AS order_count,
       ROUND(SUM(o.total), 2) AS total_spent
FROM users u
JOIN orders o ON o.user_id = u.id
GROUP BY u.id, u.first_name, u.last_name
ORDER BY order_count DESC, total_spent DESC
LIMIT 5;

SELECT p.category,
       COUNT(*)                   AS line_items,
       ROUND(SUM(oi.subtotal), 2) AS revenue
FROM order_items oi
JOIN products p ON p.id = oi.product_id
GROUP BY p.category
ORDER BY revenue DESC;

SELECT status,
       COUNT(*)             AS orders,
       ROUND(AVG(total), 2) AS avg_order_value
FROM orders
GROUP BY status
ORDER BY avg_order_value DESC;

SELECT p.id, p.name,
       SUM(oi.quantity)           AS units_sold,
       ROUND(SUM(oi.subtotal), 2) AS revenue
FROM order_items oi
JOIN products p ON p.id = oi.product_id
GROUP BY p.id, p.name
ORDER BY revenue DESC
LIMIT 5;

SELECT strftime('%Y-%m', order_date) AS month,
       COUNT(*)             AS orders,
       ROUND(SUM(total), 2) AS revenue
FROM orders
WHERE order_date >= '2024-01-01' AND order_date < '2025-01-01'
GROUP BY strftime('%Y-%m', order_date)
ORDER BY month;

SELECT user_id,
       COUNT(*) AS order_count
FROM orders
GROUP BY user_id
HAVING COUNT(*) >= 4
ORDER BY order_count DESC;

SELECT method,
       COUNT(*)              AS payments,
       ROUND(SUM(amount), 2) AS collected
FROM payments
WHERE status = 'success'
GROUP BY method
ORDER BY collected DESC;

SELECT p.category,
       ROUND(SUM(oi.subtotal), 2) AS revenue
FROM order_items oi
JOIN products p ON p.id = oi.product_id
GROUP BY p.category
HAVING SUM(oi.subtotal) > 40000
ORDER BY revenue DESC;

8 exercises, easy to challenging, on the real Playground tables.

Write each query yourself first. Every solution has been run against the Playground database, and its explanation tells you what result to expect.

1. 1Easy How many products are there in each category? Table: products

   Show solution

   SQLite · runs in the Playground

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

   ▶ Run this query in the SQL Playground

   One row per category — 10 in total. The largest is Food & Beverage with 17 products.

2. 2Easy How many employees work in each city? Show the five largest offices. Table: employees

   Show solution

   SQLite · runs in the Playground

   SELECT city, COUNT(*) AS employees
   FROM employees
   GROUP BY city
   ORDER BY employees DESC
   LIMIT 5;

   ▶ Run this query in the SQL Playground

   GROUP BY builds one group per city, ORDER BY sorts by size and LIMIT keeps five. San Francisco is first with 10 employees.

3. 3Medium What is the average salary in each department, highest first? Round to whole numbers. Table: employees

   Show solution

   SQLite · runs in the Playground

   SELECT department, ROUND(AVG(salary)) AS avg_salary
   FROM employees
   GROUP BY department
   ORDER BY avg_salary DESC;

   ▶ Run this query in the SQL Playground

   AVG is calculated separately inside each department. Customer Success has the highest average (134140).

4. 4Medium For active products only, show the total units in stock per category. Table: products

   Show solution

   SQLite · runs in the Playground

   SELECT category, SUM(stock) AS units_in_stock
   FROM products
   WHERE status = 'active'
   GROUP BY category
   ORDER BY units_in_stock DESC;

   ▶ Run this query in the SQL Playground

   WHERE filters to active products before grouping, then SUM adds each category's stock. Food & Beverage holds the most (3956 units).

5. 5Medium Count orders for every combination of year and status. Table: orders

   Show solution

   SQLite · runs in the Playground

   SELECT strftime('%Y', order_date) AS year,
          status,
          COUNT(*) AS orders
   FROM orders
   GROUP BY strftime('%Y', order_date), status
   ORDER BY year, status;

   ▶ Run this query in the SQL Playground

   Two grouping expressions create one group per year–status pair: 18 rows. The first is 2022 / cancelled with 7 orders.

6. 6Medium Which products have at least four reviews? Show the review count and average rating. Table: reviews

   Show solution

   SQLite · runs in the Playground

   SELECT product_id,
          COUNT(*)              AS reviews,
          ROUND(AVG(rating), 2) AS avg_rating
   FROM reviews
   GROUP BY product_id
   HAVING COUNT(*) >= 4
   ORDER BY reviews DESC, avg_rating DESC;

   ▶ Run this query in the SQL Playground

   HAVING keeps only products whose group has four or more rows — 6 products. Product 17 leads with 6 reviews.

7. 7Challenging Which departments have an average salary above 125,000, and how many people work in each? Table: employees

   Show solution

   SQLite · runs in the Playground

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

   ▶ Run this query in the SQL Playground

   The condition is on an aggregate, so it must be HAVING. 4 of the 10 departments qualify; Customer Success is highest at 134140.

8. 8Challenging Find the five customers who spent the most on delivered orders. Show their name, number of delivered orders and total spent. Table: users + orders

   Show solution

   SQLite · runs in the Playground

   SELECT u.first_name, u.last_name,
          COUNT(o.id)            AS delivered_orders,
          ROUND(SUM(o.total), 2) AS total_spent
   FROM users u
   JOIN orders o ON o.user_id = u.id
   WHERE o.status = 'delivered'
   GROUP BY u.id, u.first_name, u.last_name
   ORDER BY total_spent DESC
   LIMIT 5;

   ▶ Run this query in the SQL Playground

   JOIN connects customers to orders, WHERE keeps delivered orders, GROUP BY builds one group per customer, and ORDER BY with LIMIT picks the top five. Maria Johnson is first with 9759.23.

Six questions that come up often, with concise answers.

More practice across every topic: SQL interview questions and answers.

Eight patterns for quick reference.

Complete GROUP BY Syntax Reference

SELECT   column_name,             -- dimension: what to group by
         aggregate_fn(column)     -- metric: what to calculate
FROM     table_name               -- data source
WHERE    condition                -- filter individual rows first
GROUP BY column_name              -- define the groups
HAVING   aggregate_fn(column) > 0 -- filter groups after aggregation
ORDER BY column_name;             -- sort the final output