SQL Aggregation and GROUP BY Practice Questions

WHERE filters rows before grouping. HAVING filters groups after aggregation.

• WHERE cannot use aggregates like SUM or COUNT; HAVING can.
• WHERE also shrinks the data before GROUP BY runs, so it is cheaper.
• Rule of thumb: put every condition you can in WHERE, and keep HAVING for aggregate conditions.

They count different things: all rows, non-NULL values, or unique non-NULL values.

• COUNT(*): every row in the group, NULLs included.
• COUNT(column): only rows where that column is not NULL.
• COUNT(DISTINCT column): unique non-NULL values, what you want for how many customers after a join repeats them.
• Mixing them up is a common cause of inflated or understated metrics.

Every selected column must be in GROUP BY or inside an aggregate, because the database cannot pick one value for the whole group.

• Add the column to GROUP BY if you want one row per combination (shown here).
• Wrap it in MIN or MAX if any value in the group will do.
• Use a window function if you need every row next to the aggregate.
• MySQL with ONLY_FULL_GROUP_BY off silently picks a random value, which hides the bug.