GROUP BY & HAVING

Reviewed & published by Brayan K

By the end of this lesson you'll be able to turn a flat table into a summary report — counting, summing, and averaging per group with GROUP BY, then filtering those groups with HAVING. This is the leap from "show me the rows" to "show me the totals", and it's the heart of every dashboard and analytics query.

Part of the free SQL course at LearnCodingFast — hands-on lessons with examples you run in your browser, plus practice exercises and a quick quiz.

What You'll Learn

• Split a table into groups with GROUP BY
• Run COUNT, SUM and AVG once per group
• Group by one column — or several at once
• Filter whole groups with HAVING
• See why WHERE can't filter an aggregate
• Order the clauses correctly: WHERE → GROUP BY → HAVING

Our Sample Table: products

Every query in this lesson runs against this little products table — the same one from the SELECT lesson. Notice the category column: Electronics appears three times, and Kitchen, Stationery, and Home appear once each. That's what makes the grouped results easy to check by hand.

1. From One Total to One Per Group

In the last lesson, an aggregate like COUNT(*) squashed the entire table into a single number. GROUP BY changes that: it first splits the rows into groups (one per distinct value), then runs the aggregate once inside each group. You get one output row per group instead of one for the whole table.

Picture a pile of receipts. Counting the whole pile gives you one number. GROUP BY store is sorting them into a folder per store first, then counting each folder. Same counting, but now you can compare stores.

-- The table this lesson uses:
CREATE TABLE products (
    id           INTEGER PRIMARY KEY,
    product_name TEXT,
    category     TEXT,
    price        REAL,
    stock        INTEGER
);

INSERT INTO products (id, product_name, category, price, stock) VALUES
    (1, 'Wireless Mouse',      'Electronics', 24.99, 120),
    (2, 'Coffee Mug',          'Kitchen',      9.50, 300),
    (3, 'Mechanical Keyboard', 'Electronics', 79.00,  45),
    (4, 'Notebook',            'Stationery',   3.25, 500),
    (5, 'Desk Lamp',           'Home',        32.00,  80),
    (6, 'USB-C Cable',         'Electronics', 12.99, 200);

-- WITHOUT GROUP BY, an aggregate collapses the WHOLE table to one number
SELECT COUNT(*) AS total_products
FROM products;

-- Our table has 6 rows, so this returns a single value: 6.
-- Useful, but it tells you nothing about each category.

-- ✅ Expected output:
-- total_products
-- 6

-- ✅ Expected result:
-- total_products
-- 6

2. GROUP BY with COUNT — One Aggregate Per Group

Add GROUP BY category and SQL builds one bucket per category, then counts the rows in each. Electronics holds ids 1, 3 and 6, so its count is 3; the other three categories have a single product each. The result has exactly one row per distinct category.

-- Uses the products table from the first example.
-- WITH GROUP BY, the aggregate runs once PER group
SELECT category, COUNT(*) AS product_count
FROM products
GROUP BY category;

-- SQL splits the 6 rows into one bucket per category,
-- then counts the rows inside each bucket.
-- One output row per distinct category.

-- ✅ Expected result:
-- category | product_count
-- Electronics | 3
-- Home | 1
-- Kitchen | 1
-- Stationery | 1

Your Turn: count per category

Fill in the two blanks to count how many products sit in each category. One blank is the aggregate, the other is the clause that makes the buckets. The expected result is in the comments so you can check yourself.

-- Uses the products table from the first example.
-- 🎯 YOUR TURN — fill in the two blanks, then press "Try it Yourself"
-- Goal: count how many products are in each category.

SELECT category, ___ AS product_count   -- 👉 the aggregate that counts rows: COUNT(*)
FROM products
___ category;                           -- 👉 the clause that makes the buckets: GROUP BY

-- ✅ Expected result (4 rows):
--    Electronics | 3 ,  Kitchen | 1 ,  Stationery | 1 ,  Home | 1

3. SUM and AVG Per Group

Any aggregate works with GROUP BY, not just COUNT. Swap in SUM(stock) to total the stock in each category, or AVG(price) to average the prices. For single-product categories the "average" and "sum" are just that one product's value — only Electronics actually combines several rows.

-- Uses the products table from the first example.
-- A different aggregate, same grouping: total stock per category
SELECT category, SUM(stock) AS total_stock
FROM products
GROUP BY category;

-- For each category bucket, add up the stock column.
-- Electronics = 120 + 45 + 200 = 365, the rest have one row each.

-- ✅ Expected result:
-- category | total_stock
-- Electronics | 365
-- Home | 80
-- Kitchen | 300
-- Stationery | 500

-- Uses the products table from the first example.
-- AVG gives the average price inside each group
SELECT category, ROUND(AVG(price), 2) AS avg_price
FROM products
GROUP BY category;

-- Electronics averages three prices: (24.99 + 79.00 + 12.99) / 3.
-- ROUND(..., 2) trims the result to 2 decimal places.

-- ✅ Expected result:
-- category | avg_price
-- Electronics | 38.99
-- Home | 32
-- Kitchen | 9.5
-- Stationery | 3.25

4. Grouping by More Than One Column

List several columns after GROUP BY and SQL makes a bucket for each unique combination. GROUP BY category, supplier would give one row per category-and-supplier pair. Our table only has the columns above, but the rule is the same: more grouping columns means smaller, more specific groups.

Rule of thumb: the number of output rows equals the number of distinct combinations of your GROUP BY columns.

5. HAVING — Filter the Groups

HAVING filters the groups after they've been aggregated. It looks like WHERE, but it can test aggregate values such as COUNT(*) > 1 or AVG(price) > 20. WHERE runs before grouping, so it has no idea what a group's count is yet — that's exactly the job HAVING exists to do.

-- Uses the products table from the first example.
-- HAVING filters the GROUPS after they are counted/summed
SELECT category, COUNT(*) AS product_count
FROM products
GROUP BY category
HAVING COUNT(*) > 1;

-- WHERE can't see COUNT(*) (it runs before grouping), so you must
-- use HAVING. Only Electronics has more than one product.

-- ✅ Expected result:
-- category | product_count
-- Electronics | 3

Your Turn: keep the busy categories

One blank this time — add the keyword that filters groups so only categories with more than one product survive. Remember: it's not WHERE.

-- Uses the products table from the first example.
-- 🎯 YOUR TURN — add ONE line to keep only the busy categories.
-- Goal: show categories that have more than one product.

SELECT category, COUNT(*) AS product_count
FROM products
GROUP BY category
___ COUNT(*) > 1;     -- 👉 the keyword that filters GROUPS (not WHERE!)

-- ✅ Expected result (1 row): Electronics | 3

6. The Logical Clause Order

When a query has all of these, the database runs them in a fixed logical order. You write them in this order too, or you'll get a syntax error:

SELECT   columns / aggregates
FROM     table
WHERE    row_conditions      -- 1. filter ROWS (before grouping)
GROUP BY columns             -- 2. build the groups
HAVING   group_conditions    -- 3. filter GROUPS (after aggregating)
ORDER BY columns             -- 4. sort the result
LIMIT    n;                  -- 5. cap the rows

The query below uses three of these steps together. Walk the comments line by line to see which rows survive each stage.

-- Uses the products table from the first example.
-- WHERE and HAVING work TOGETHER, in this order:
--   WHERE  filters individual ROWS   (before grouping)
--   GROUP BY makes the buckets
--   HAVING filters whole GROUPS       (after aggregating)
SELECT category, COUNT(*) AS in_stock_lines, SUM(stock) AS total_stock
FROM products
WHERE stock >= 100          -- 1. keep only well-stocked rows (drops id 3 & 5)
GROUP BY category           -- 2. bucket the survivors by category
HAVING SUM(stock) > 300;    -- 3. keep buckets whose stock total beats 300

-- After WHERE: Electronics(120,200), Kitchen(300), Stationery(500) remain.
-- Electronics total = 320, Stationery = 500 → both pass; Kitchen = 300 fails.

-- ✅ Expected result:
-- category | in_stock_lines | total_stock
-- Electronics | 2 | 320
-- Stationery | 1 | 500

Common Errors (and the fix)

• Selecting a column that isn't grouped or aggregated: SELECT category, product_name FROM products GROUP BY category; fails (or returns a random name) — which of the three Electronics names should it show? Every selected column must appear in GROUP BY or sit inside an aggregate like COUNT()/MAX().
• Using WHERE to filter an aggregate: WHERE COUNT(*) > 1 errors with "misuse of aggregate" — WHERE runs before grouping. Move that test to HAVING COUNT(*) > 1.
• HAVING without GROUP BY: it's only meaningful once groups exist. Add the matching GROUP BY category, or if you truly want to filter rows, use WHERE instead.
• Clauses in the wrong order: HAVING before GROUP BY, or WHERE after GROUP BY, is a syntax error. The order is always WHERE → GROUP BY → HAVING → ORDER BY.

Frequently Asked Questions

Q: What's the real difference between WHERE and HAVING?

WHERE filters rows before grouping, so it can't reference an aggregate. HAVING filters whole groups after aggregation, so it can test things like COUNT(*) or AVG(price). They often appear in the same query.

Q: Why do I get an error when I SELECT a column that isn't in GROUP BY?

Because the group might contain many different values for that column (three product names for Electronics, say), and SQL can't pick one. Put the column in GROUP BY, or wrap it in an aggregate.

Q: Can I use a column alias inside HAVING?

It varies by database. To stay portable, repeat the aggregate — write HAVING COUNT(*) > 1 rather than HAVING product_count > 1.

Q: Does GROUP BY sort the results?

Not guaranteed. Some databases happen to return groups in order, but if you need a specific order, add an explicit ORDER BY after the HAVING clause.

Mini-Challenge: Premium Categories

Put it all together — a brief, a blank canvas, and the expected result in the comments. Write it, then copy it into a playground to confirm. You'll need GROUP BY, AVG, and HAVING in one query.

-- Uses the products table from the first example.
-- 🎯 MINI-CHALLENGE
-- Using ONLY what this lesson covered (GROUP BY, AVG, HAVING):
--   1. Show category and a column avg_price = ROUND(AVG(price), 2)
--   2. Group by category
--   3. Keep only the categories whose average price is above 20
--
-- ✅ Expected result (2 rows):
--    Electronics | 38.99 ,  Home | 32.00
--    (Kitchen 9.50 and Stationery 3.25 are filtered out by HAVING)

-- your query here

🎉 Lesson Complete

• ✅ GROUP BY splits the table into groups and runs each aggregate once per group
• ✅ COUNT, SUM, and AVG all work per group — one output row per group
• ✅ Listing several columns in GROUP BY groups by each unique combination
• ✅ HAVING filters groups on an aggregate; WHERE filters rows before grouping
• ✅ The order is always WHERE → GROUP BY → HAVING → ORDER BY
• ✅ Next: Subqueries — nesting one query inside another for even more powerful results

Practice quiz

Continue this course

• Previous: Aggregate Functions
• Next: The HAVING Clause — Filter grouped results — WHERE for groups
• Quick reference: SQL cheat sheet › Aggregation