Aggregate Functions, GROUP BY and HAVING

Aggregate Functions

Aggregate functions collapse many rows into a single summary value.

Function	Description
COUNT(*)	Number of rows
SUM(col)	Total of a numeric column
AVG(col)	Average of a numeric column
MIN(col)	Smallest value
MAX(col)	Largest value

SELECT COUNT(*) FROM employees;
SELECT SUM(salary) FROM employees;
SELECT AVG(salary) FROM employees WHERE department = 'Engineering';
SELECT MIN(salary), MAX(salary) FROM employees;

GROUP BY

Runs an aggregate function per group instead of over the whole table — the PostgreSQL equivalent of MongoDB's $group stage.

SELECT department, COUNT(*) AS headcount
FROM employees
GROUP BY department;

 department  | headcount
--------------+-----------
 Engineering  |         4
 Sales        |         2
 HR           |         1

SELECT department, AVG(salary) AS avg_salary
FROM employees
GROUP BY department
ORDER BY avg_salary DESC;

Every non-aggregated column in SELECT must appear in GROUP BY — Postgres enforces this, unlike some other databases.

HAVING — Filtering Groups

WHERE filters rows before grouping; HAVING filters groups after aggregation — you can't use WHERE to filter on an aggregate result like COUNT(*).

SELECT department, COUNT(*) AS headcount
FROM employees
GROUP BY department
HAVING COUNT(*) > 2;
-- only departments with more than 2 employees

SELECT department, AVG(salary) AS avg_salary
FROM employees
GROUP BY department
HAVING AVG(salary) > 50000;

You can combine WHERE and HAVING in the same query — WHERE narrows the rows first, then HAVING narrows the resulting groups:

SELECT department, AVG(salary) AS avg_salary
FROM employees
WHERE hired_at > '2023-01-01'
GROUP BY department
HAVING AVG(salary) > 50000;

Common Errors & Troubleshooting

• ERROR: column "employees.name" must appear in the GROUP BY clause or be used in an aggregate function — every plain (non-aggregated) column in SELECT must be listed in GROUP BY — either add it there, or wrap it in an aggregate like MAX(name) if that's what you meant.
• Using WHERE to filter on an aggregate result — WHERE COUNT(*) > 2 is invalid; aggregate filtering must use HAVING, since WHERE runs before grouping happens.
• AVG() including unexpected rows — remember AVG() ignores NULL values but not zeros; a column with legitimate 0 values will pull the average down, which is sometimes mistaken for a NULL-handling bug.

Key Takeaways

• Aggregate functions summarize many rows into one value; GROUP BY runs them per-group instead of over everything.
• WHERE filters rows before grouping; HAVING filters groups after aggregation — they're not interchangeable.
• Every plain column in SELECT alongside an aggregate must be listed in GROUP BY.