SQL › Grouping Your Results

GROUP BY and HAVING

GROUP BY splits the rows into groups that share a value, and the aggregates then work out one row per group. HAVING filters those groups.

Syntax

Order of execution: FROM (and joins) → WHERE → GROUP BY → HAVING → SELECT → ORDER BY

WHERE filters rows before they're grouped; HAVING filters the groups afterwards.

Code
SELECT column1, AGG(column2) AS alias
FROM table_name
WHERE row_condition
GROUP BY column1
HAVING group_condition
ORDER BY column1;

Examples

One row per group

Each payment method becomes one row with its own totals. To check a group, count its rows on their own with WHERE.

Code
SELECT method, COUNT(*) AS payments, SUM(amount) AS total
FROM payments
GROUP BY method
ORDER BY method;
 
-- Check one group: the same count as the wallet row above
SELECT COUNT(*) AS payments FROM payments WHERE method = 'wallet';

Grouping by several columns

Every pair of values gets its own row.

Code
SELECT method, status, COUNT(*) AS payments
FROM payments
GROUP BY method, status
ORDER BY method, status;

Every column grouped or summarised

Code
-- Fails: status is neither grouped nor inside an aggregate
SELECT method, status, COUNT(*) FROM payments GROUP BY method;

Grouping by an expression

Group by a calculated value, such as the year of a date.

Code
SELECT EXTRACT(YEAR FROM order_date) AS year, COUNT(*) AS orders
FROM orders
GROUP BY EXTRACT(YEAR FROM order_date)
ORDER BY year;

WHERE vs HAVING

WHERE drops rows before grouping; HAVING drops groups after.

Code
SELECT product_id, COUNT(*) AS good_reviews
FROM reviews
WHERE rating >= 4                -- only reviews of 4 or 5 stars
GROUP BY product_id
HAVING COUNT(*) >= 2             -- only products with 2 or more of them
ORDER BY good_reviews DESC, product_id;
 
-- Fails: HAVING runs before SELECT, so it can't use the alias (MySQL allows it)
SELECT product_id, COUNT(*) AS good_reviews FROM reviews GROUP BY product_id HAVING good_reviews >= 2;

Groups over a join

Join first, then group the joined rows.

Code
SELECT p.product_name, SUM(oi.quantity) AS units
FROM order_items oi
JOIN products p ON p.product_id = oi.product_id
GROUP BY p.product_id, p.product_name
ORDER BY units DESC, p.product_name
LIMIT 5;

Groups with no matching rows

After a LEFT JOIN, count a column from the right table: its NULLs aren't counted.

Code
SELECT c.full_name,
       COUNT(r.review_id) AS reviews,   -- 0 for customers without reviews
       COUNT(*) AS rows_in_group        -- 1 for them: the row the LEFT JOIN kept
FROM customers c
LEFT JOIN reviews r ON r.customer_id = c.customer_id
GROUP BY c.customer_id, c.full_name
ORDER BY c.customer_id;

Rows that repeat

Group by every column that makes two rows the same, and HAVING COUNT(*) > 1 keeps only the values that appear more than once. Leave one of those columns out, and rows that differ in it are counted as the same.

Code
-- Customers who gave the same rating more than once
SELECT customer_id, rating, COUNT(*) AS reviews
FROM reviews
GROUP BY customer_id, rating
HAVING COUNT(*) > 1
ORDER BY customer_id, rating;

A group's values in one text

STRING_AGG(column, ', ') joins the values of each group into one text, with the separator between them. Put ORDER BY inside the brackets to fix their order; without it, any order is possible.

MySQL writes GROUP_CONCAT(column ORDER BY column SEPARATOR ', '), Oracle LISTAGG(column, ', ') WITHIN GROUP (ORDER BY column), and SQL Server (2017 and later) STRING_AGG(column, ', ') WITHIN GROUP (ORDER BY column).

Code
-- Every city's customers, in alphabetical order
SELECT city, COUNT(*) AS customers, STRING_AGG(full_name, ', ' ORDER BY full_name) AS names
FROM customers
GROUP BY city
ORDER BY city;

Rows to columns (pivot)

A pivot turns the values of one column into columns of their own, such as one column per year. Group by what each row should be, and give every new column its own SUM with a CASE inside, so it only adds up that column's rows.

ELSE 0 shows 0 where a group has no rows for that column; without it, you get NULL. A plain SUM of the same column adds a total column.

Code
-- One row per shipping city, one column per year
SELECT shipping_city,
       SUM(CASE WHEN EXTRACT(YEAR FROM order_date) = 2024 THEN total_amount ELSE 0 END) AS revenue_2024,
       SUM(CASE WHEN EXTRACT(YEAR FROM order_date) = 2025 THEN total_amount ELSE 0 END) AS revenue_2025,
       SUM(total_amount) AS all_years
FROM orders
GROUP BY shipping_city
ORDER BY shipping_city;

SUM(CASE …) works in every database. SQL Server and Oracle also have a PIVOT operator, and PostgreSQL has a crosstab function in its tablefunc extension; both still need the list of new columns written out.

Remember

• Every column in SELECT is either in GROUP BY or inside an aggregate.
• WHERE filters rows before grouping; HAVING filters groups after it.
• HAVING can't use a SELECT alias (except in MySQL); repeating the aggregate works everywhere.
• After a LEFT JOIN, COUNT(right_table.column) gives 0 for empty groups; COUNT(*) gives 1.
• To find repeats, group by every column that defines one and keep HAVING COUNT(*) > 1.
• STRING_AGG keeps a fixed order only with an ORDER BY inside its brackets.

Practice

1. Cities With Several Customers (Easy)
2. Products in Every Category (Easy)
3. Big Spenders of 2025 (Medium)
4. Orders Saved Twice (Easy)
5. Each Order's Products in One Line (Easy)