Beginner level · Module 4: Aggregation and Reporting Queries · Lesson 2 of 3

GROUP BY and HAVING

Summarise data per group and filter the groups.

In this lesson you will

Group rows with GROUP BY
Filter groups with HAVING

Key concepts

GROUP BYone row per groupHAVINGWHERE before grouping

The lesson

GROUP BY class produces one result row per class, and aggregates are calculated within each group: SELECT class, COUNT(*) FROM students GROUP BY class.

Every column in the SELECT list must be grouped or aggregated; otherwise the database cannot know which value to show.

WHERE filters rows before grouping; HAVING filters groups afterwards using aggregates: … GROUP BY branch HAVING SUM(amount) > 15000 keeps only branches above that total.

Step by step

Count students per class.
Total payments per student.
Keep only students who have paid more than a threshold with HAVING.
Combine WHERE and HAVING in one query.

Code example

Branch sales above target with GROUP BY and HAVING

Total, count and average sales per branch, keeping only branches above 15,000 SSP.

SQLbranch_report.sql

CREATE TABLE sales (  id INTEGER PRIMARY KEY,  branch TEXT NOT NULL,  item TEXT NOT NULL,  amount INTEGER NOT NULL); INSERT INTO sales (branch, item, amount) VALUES  ('Juba', 'rice', 14000), ('Juba', 'oil', 9000), ('Juba', 'soap', 4800),  ('Wau', 'rice', 10500), ('Wau', 'sugar', 5600),  ('Malakal', 'rice', 7000); -- Branches with sales above 15,000 SSP, with their number of sales and average saleSELECT branch,       COUNT(*) AS sales,       SUM(amount) AS total,       ROUND(AVG(amount)) AS averageFROM salesGROUP BY branchHAVING SUM(amount) > 15000ORDER BY total DESC;

Output

branch  sales  total  average
------  -----  -----  -------
Juba        3  27800     9267
Wau         2  16100     8050

How it works

GROUP BY branch produces one row per branch with COUNT, SUM and AVG calculated inside each group. HAVING filters after grouping, so Malakal (7,000 SSP) disappears — WHERE could not do this, because the total does not exist until the rows are grouped. Juba's average is 27,800 ÷ 3 = 9,266.67, rounded to 9,267. Output produced by running the script in SQLite; figures are illustrative.

Key concepts

GROUP BYCOUNT, SUM, AVGHAVING vs WHEREROUND

Practice activity

Report total collections per class and list classes whose collections are below target.

Expected result: Totals per class are correct and only classes below target appear in the second query.

Check your understanding

When is HAVING needed instead of WHERE?

Show answer
When filtering on an aggregate, after grouping.
What does GROUP BY class produce?

Show answer
One result row per class.

Taught live by an instructor — online or in person. Free course lessons are for self-study and do not lead to a certificate — see registration options.