GROUP BY and aggregates

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 PostgreSQL says so rather than guess ("must appear in the GROUP BY clause or be used in an aggregate function"). It makes one exception: once you group by a table's primary key, that table's other columns are allowed, because the key already fixes them.

Try it

1CREATE TABLE customers (2  id      int PRIMARY KEY,3  name    text NOT NULL,4  country text NOT NULL,5  credit  int6);7INSERT INTO customers VALUES8  (1, 'Nino Beridze', 'GE', 500),9  (2, 'Ana Kapanadze', 'GE', 1200),10  (3, 'Luka Meladze', 'DE', 0),11  (4, 'Mari Tsiklauri', 'PL', 300),12  (5, 'Giorgi Abashidze', 'GE', NULL);

1SELECT2  country,3  COUNT(*)        AS rows_in_group,4  COUNT(credit)   AS with_credit,5  COALESCE(SUM(credit), 0) AS total_credit6FROM customers7GROUP BY country8ORDER BY country;

COUNT(*) against COUNT(column)

• COUNT(*) counts rows.
• COUNT(credit) counts rows where credit is 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. A row condition on a grouped column is allowed in HAVING, but it says the wrong thing: it reads as "drop these groups" when you meant "never group these rows". Put row conditions in , where they say what they mean and the rows are gone before any grouping work starts.

1CREATE TABLE customers (2  id      int PRIMARY KEY,3  name    text NOT NULL,4  country text NOT NULL,5  credit  int6);7INSERT INTO customers VALUES8  (1, 'Nino Beridze', 'GE', 500),9  (2, 'Ana Kapanadze', 'GE', 1200),10  (3, 'Luka Meladze', 'DE', 0),11  (4, 'Mari Tsiklauri', 'PL', 300),12  (5, 'Giorgi Abashidze', 'GE', NULL);

1SELECT2  AVG(credit)              AS avg_known,3  AVG(COALESCE(credit, 0)) AS avg_all_rows4FROM customers5WHERE country = 'GE';

• Selecting a column that is neither grouped nor aggregated. PostgreSQL and SQL Server refuse the query; MySQL with ONLY_FULL_GROUP_BY switched off returns a value from an arbitrary row of the group.
• Expecting SUM of no rows to be 0. It is NULL. Wrap it in COALESCE(SUM(x), 0) when a report needs a number, as the example above does.

What interviewers ask

• What is the difference between WHERE and HAVING? WHERE filters rows before grouping and cannot see aggregates; HAVING filters the groups afterwards.
• COUNT(*), COUNT(col), COUNT(DISTINCT col) — what does each count? Rows; rows where col is not null; distinct non-null values of col.
• How do you find duplicate emails in a table? GROUP BY email HAVING COUNT(*) > 1.