Practical guide

SQL GROUP BY and COUNT for Simple Reports

GROUP BY turns detailed rows into one summary row per group. Every selected expression should either be grouped or aggregated, subject to your database's rules.

What to do

  1. Count customers in each city with SELECT city, COUNT(*) AS customer_count FROM customers GROUP BY city;.
  2. Filter groups after aggregation with HAVING, such as HAVING COUNT(*) > 10.
  3. Use WHERE to filter source rows before groups are calculated, and HAVING to filter the resulting groups.
  4. Compare the sum of grouped counts with the count of the filtered source rows to check that the report covers the intended data.

Checks and limitations

  • COUNT(*) counts rows; COUNT(column) ignores rows where that column is NULL.
  • Some database systems require every non-aggregated selected column to appear in GROUP BY.