SQL HAVING: Filter Group Totals After Filtering Source Rows

The practice task

Find departments with at least 100 approved training credits. A declined record must not contribute to the total. Use a fictional small ledger with one department exactly on the threshold.

The data below is fictional. Run the example in a new SQLite database. It was checked with SQLite 3.51.0; other databases may use different syntax, functions or type rules.

Complete worked query

Create the small fixture and run the query together. The IDs, boundary values and missing entries make the result small enough to check by hand.

CREATE TABLE credits(dept TEXT, amount INTEGER, status TEXT);
INSERT INTO credits VALUES ('Design',60,'approved'),('Design',40,'approved'),('Design',80,'declined'),('Support',90,'approved'),('Research',120,'approved');

SELECT dept, SUM(amount) AS approved_credits FROM credits
WHERE status = 'approved'
GROUP BY dept
HAVING SUM(amount) >= 100
ORDER BY dept;

Checked result

This is the result of the exact example above. NULL marks a missing database value rather than the text string "NULL".

Read the result

WHERE removes the declined Design record before any department total is calculated. Design then has 100 credits, Research has 120 and Support has 90. HAVING keeps Design and Research because the threshold is inclusive.

Putting the threshold on each raw amount would lose the two Design records even though their combined approved value qualifies. A group condition should evaluate the group calculation, not a substitute condition on each contributing row.

When reading an unfamiliar report, separate its population from its selection rule. Here the population is approved credit records. The selection rule is a department sum of at least 100. A query can look plausible while using the right threshold on the wrong population.

Mistakes to check

Use these checks when adapting the example to a different dataset.

• Do not use WHERE SUM(amount) >= 100 for this grouped task.
• Repeat the aggregate expression when portability of a result alias is uncertain.
• Test a group exactly at the threshold as well as one just below it.