mindixx
mindixx

Reference · module 4

Totals and groups

Everything this module explains, on one page.

COUNT, SUM, AVG

Turn many rows into one number.

  • Aggregate functions turn many rows into one value. SELECT COUNT(*) FROM orders; returns how many rows there are, while COUNT(phone) counts only rows where phone is not NULL.
  • SUM(total) adds a column up and AVG(total) gives its average. Both skip NULL values, so the average of 10, NULL and 20 is 15, not 10.
  • MIN and MAX find the smallest and largest value, and they work on text and dates too. WHERE runs before the aggregate: SELECT MAX(total) FROM orders WHERE paid = 1;.

The mistake you are about to make

SELECT COUNT(phone) FROM customers;

SELECT COUNT(*) FROM customers;

It seems not to matter what goes inside COUNT — rows are rows. COUNT(column) skips NULL: customers without a phone drop out, and you get fewer customers than there are.

GROUP BY

Get one total per group instead of one for the whole table.

  • GROUP BY splits rows into groups and runs the aggregate on each: SELECT city, COUNT(*) FROM customers GROUP BY city; gives one row per city with its number of customers.
  • Each group becomes exactly one row. So next to aggregates you select only the grouping columns: a whole city has no single name.
  • Group by several columns to get finer groups: GROUP BY city, year gives one row for each city and year that appear together in the table.

The mistake you are about to make

SELECT city, name, COUNT(*) FROM customers GROUP BY city;

SELECT city, COUNT(*) FROM customers GROUP BY city;

You want the name too while you are at it. Strict databases reject this query; SQLite runs it and picks some customer's name from the group — an answer that looks true.

HAVING

Filter groups, and know which filter runs when.

  • HAVING filters groups, just as WHERE filters rows: GROUP BY city HAVING COUNT(*) >= 2 keeps only the cities with at least two customers.
  • The order of work is fixed: WHERE drops rows first, then GROUP BY forms groups, then HAVING drops groups. That is why WHERE cannot use COUNT — the groups do not exist yet.
  • Groups sort like rows: ORDER BY COUNT(*) DESC puts the biggest group first. A full query reads SELECT, FROM, WHERE, GROUP BY, HAVING, ORDER BY, LIMIT.

The mistake you are about to make

SELECT city FROM customers WHERE COUNT(*) > 1 GROUP BY city;

SELECT city FROM customers GROUP BY city HAVING COUNT(*) > 1;

A condition is a condition, and WHERE is where conditions usually go. WHERE runs before grouping, when there is nothing to count yet, and the database replies misuse of aggregate.

Back to the map