How to use GROUP BY and aggregate functions

· Category: SQL & Databases

Short answer

GROUP BY collapses rows that share the same values in specified columns into summary rows. Aggregate functions like COUNT, SUM, AVG, MIN, and MAX compute values per group.

Steps

  1. Count per group: SELECT department, COUNT(*) FROM employees GROUP BY department;
  2. Sum values: SELECT department, SUM(salary) FROM employees GROUP BY department;
  3. Average: SELECT department, AVG(salary) FROM employees GROUP BY department;
  4. Filter groups with HAVING: SELECT department, AVG(salary) FROM employees GROUP BY department HAVING AVG(salary) > 50000;
  5. Use multiple grouping columns: GROUP BY year, month

Tips

  • Columns in SELECT that are not aggregated must appear in GROUP BY.
  • HAVING filters groups after aggregation; WHERE filters rows before aggregation.

Common issues

  • Including non-aggregated, non-grouped columns in SELECT causes an error in strict SQL modes.
  • NULL values form their own group and affect COUNT(*) versus COUNT(column).r