Grouping: GROUP BY, aggregates, HAVING

So far every query returned individual rows. Analytics is mostly about summaries: totals, averages, counts per group. That is GROUP BY.

Aggregate functions

An aggregate collapses many rows into one value:

Function	Returns
COUNT(*)	number of rows
COUNT(col)	number of non-NULL values
COUNT(DISTINCT col)	number of unique values
SUM(col)	total
AVG(col)	average
MIN(col) / MAX(col)	smallest / largest

Without grouping, an aggregate summarizes the whole table:

SELECT COUNT(*) AS offers, AVG(salary_usd) AS avg_salary
FROM job_offers;

Note: aggregates skip NULLs. AVG(score) averages only completed enrollments.

GROUP BY

GROUP BY splits rows into groups and runs the aggregate per group:

SELECT country, COUNT(*) AS student_count
FROM students
GROUP BY country;

The golden rule: every column in SELECT must be either inside an aggregate or listed in GROUP BY. Postgres enforces this — the error message will become an old friend.

Group by several columns to get finer groups:

SELECT category, difficulty, COUNT(*)
FROM courses
GROUP BY category, difficulty;

HAVING: filtering groups

WHERE filters rows before grouping. It cannot use aggregates. To filter the groups themselves, use HAVING:

SELECT category, COUNT(*) AS course_count
FROM courses
GROUP BY category
HAVING COUNT(*) > 2;

Both can appear in one query — and now you know the full order of clauses:

SELECT city, AVG(salary_usd) AS avg_salary
FROM job_offers
WHERE remote = false          -- 1. filter rows
GROUP BY city                 -- 2. group them
HAVING COUNT(*) >= 2          -- 3. filter groups
ORDER BY avg_salary DESC      -- 4. sort
LIMIT 5;                      -- 5. cut

This single shape — filter, group, filter groups, sort, limit — answers a huge share of real business questions. Congratulations: you have finished Basics of selection I.