Vol. I · A Postgres Handbook

The Query & the Schema

A reading-room reference for writing PostgreSQL by hand — selects to window functions, with the schema in plain view.

05

Chapter 5

GROUP BY & Aggregation

Folding rows into summaries. GROUP BY, HAVING, ROLLUP, and the aggregate functions that turn data into answers.

In this chapter

24

queries, easy → advanced

  1. 001Count employees by departmentRead
  2. 002Sum total sales per customerRead
  3. 003Find average salary by job titleRead
  4. 004Monthly revenue reportRead
  5. 005Products sold per categoryRead
  6. 006HAVING: departments with budget over 1 millionRead
  7. 007ROLLUP: hierarchical sales totalsRead
  8. 008CUBE: all combinations of groupingsRead
  9. 009GROUPING SETS: custom aggregation combinationsRead
  10. 010Percentile: find median salaryRead
  11. 011STRING_AGG: list employee names per departmentRead
  12. 012FILTER clause in aggregationRead
  13. 013Mode: most common salary valueRead
  14. 014Pivot-style aggregation using CASERead
  15. 015Weighted average calculationRead
  16. 016Revenue by month using TO_CHARRead
  17. 017Average order value by customer tierRead
  18. 018Salary distribution by bandRead
  19. 019Top 3 products by revenue in each categoryRead
  20. 020Daily active users (orders per day)Read
  21. 021Cohort size by signup monthRead
  22. 022Product pairs frequently bought togetherRead
  23. 023Classic: department with highest average salaryRead
  24. 024Histogram: salary distribution with width_bucketRead