SQL: grouping & aggregates
This course covers grouping and aggregating rows in PostgreSQL, using a small orders table with three statuses. It explains how GROUP BY folds rows into one row per distinct value, and why every other selected column must be an aggregate. It covers COUNT(*), COUNT(column) and COUNT(DISTINCT), plus SUM and AVG with decimal results. HAVING filters groups after WHERE filters rows. The same queries are then written in ActiveRecord with .group, .count, .sum, .average and .having, and compared with adding up loaded rows in Ruby.
What you'll learn
- ✓How GROUP BY collapses rows and why other columns must be aggregates
- ✓The difference between COUNT(*), COUNT(column) and COUNT(DISTINCT column)
- ✓Totals and averages per group with SUM and AVG
- ✓Filtering groups with HAVING and the order WHERE, GROUP BY, HAVING
- ✓Grouping and aggregating in ActiveRecord with .group, .count, .sum, .average and .having
- ✓Why aggregating in the database does less work than summing rows in Ruby
GROUP BY: collapsing rows into groups
Six rows went into that table: three "shipped", two "pending", one "cancelled". SQL: where & operators already showed how to pull out one status at a time, WHERE status = 'shipped' matching those three rows. GROUP BY asks a different question: not "give me the shipped ones," but "give me one row…
Read the full lesson free →Curriculum
7 lessonsGROUP BY: collapsing rows into groups
GROUP BY folds rows sharing a value into one row, and WHERE filters rows before grouping.
COUNT: counting rows and values
COUNT(*) counts every row, COUNT(column) skips NULLs, and COUNT(DISTINCT) counts how many different values appear.
SUM & AVG: totals and averages per group
SUM totals a column per group or for the whole table, and AVG returns a decimal result.
HAVING: filtering after aggregation
HAVING filters groups after GROUP BY, since WHERE runs earlier and cannot reference an aggregate like COUNT(*).
GROUP BY in ActiveRecord: .group, .count, .sum
ActiveRecord's .group with .count, .sum, .average and .having returns per-group hashes matching the raw SQL results.
Aggregate in the database, not in Ruby
Summing in Ruby loads and discards every full row, while .sum lets PostgreSQL return one number.
Putting it together: grouping and aggregating
One SQL query combines WHERE, GROUP BY, COUNT, SUM and HAVING, and ActiveRecord translates its counting part.