Aggregate at the right grain

The idea

Aggregation changes the grain of a result: several input rows become one summary row. State that grain explicitly, such as one row per team, before selecting expressions. GROUP BY defines the groups. WHERE filters individual input rows before aggregation, while HAVING filters the groups after aggregate values are available. Mixing these stages often answers a different question.

After a left join, COUNT(*) includes the preserved parent row even when no child matched. Counting a non-NULL child key instead yields zero for that case. Avoid selecting arbitrary non-grouped columns in a grouped report, even though SQLite permits some forms: the value may not represent the intended row. Consider join multiplication before SUM, and test groups with zero, one, and several matching items.

WITH sales(team,amount) AS (VALUES('A',4),('A',6),('B',3))
SELECT team, SUM(amount) AS total
FROM sales GROUP BY team
HAVING SUM(amount) >= 5
ORDER BY team;

team | total
A    | 10

Aggregation produces totals of ten and three. HAVING then retains only the group whose total reaches the threshold.