GROUP BY and aggregates
Collapsing many rows into one number, and the difference between WHERE and HAVING.
After this lesson you can
- Count, sum and average over groups of rows
- Say what COUNT(*) counts that COUNT(column) does not
- Choose between WHERE and HAVING and explain why
An aggregate turns many rows into one value. GROUP BY says which rows
belong together.
SELECT country, COUNT(*) AS customers, SUM(credit) AS total_credit
FROM customers
GROUP BY country;
Every column in the SELECT list must either be in the GROUP BY or be
wrapped in an aggregate. Anything else has no single answer per group, and
Postgres will say so rather than guess.
Try it
CREATE TABLE customers ( id int PRIMARY KEY, name text NOT NULL, country text NOT NULL, credit int);INSERT INTO customers VALUES (1, 'Nino Beridze', 'GE', 500), (2, 'Ana Kapanadze', 'GE', 1200), (3, 'Luka Meladze', 'DE', 0), (4, 'Mari Tsiklauri', 'PL', 300), (5, 'Giorgi Abashidze', 'GE', NULL);SELECT country, COUNT(*) AS rows_in_group, COUNT(credit) AS with_credit, COALESCE(SUM(credit), 0) AS total_creditFROM customersGROUP BY countryORDER BY country;COUNT(*) against COUNT(column)
COUNT(*)counts rows.COUNT(credit)counts rows wherecreditis not null.COUNT(DISTINCT country)counts distinct non-null values.
So COUNT(*) and COUNT(credit) differ by exactly the number of nulls, and
a report that quietly uses the wrong one is off by that much. SUM and
AVG also skip nulls, which is why an average over a nullable column is an
average of the rows that had a value, not of all the rows.
WHERE against HAVING
WHERE filters rows before they are grouped. HAVING filters groups
after. That ordering is the whole answer:
SELECT country, COUNT(*) AS customers
FROM customers
WHERE credit IS NOT NULL -- drops rows
GROUP BY country
HAVING COUNT(*) > 1; -- drops groups
You cannot put an aggregate in WHERE, because at that point the groups do
not exist yet. Putting a plain row condition in HAVING usually works and
is slower, because the database grouped rows it was about to throw away.
Try it yourself
2 visible tests · 2 hidden testsTable customers(id, name, country). Return country and customers
(how many customers are in it), but only for countries with more than
one customer. Order by country ascending.
CREATE TABLE customers ( id int PRIMARY KEY, name text NOT NULL, country text NOT NULL);INSERT INTO customers VALUES (1, 'Nino', 'GE'), (2, 'Ana', 'GE'), (3, 'Luka', 'DE'), (4, 'Mari', 'PL'), (5, 'Giorgi', 'GE');Sign up to check the hidden tests and save your progress. Sign up