Core SQL: Querying and Manipulating DataLesson 2.3

How SQL aggregate functions work in PostgreSQL

COUNT SUM AVG MIN MAX, GROUP BY, HAVING, COUNT DISTINCT, filter with FILTER clause, NULL in aggregates

Aggregate Functions

Aggregates collapse many rows into one summary value. They are the foundation of all reporting queries.

Common aggregates

SELECT
  department_id,
  COUNT(*)              AS total_employees,
  COUNT(DISTINCT title) AS unique_titles,
  AVG(salary)           AS avg_salary,
  MAX(salary)           AS top_salary,
  MIN(hire_date)        AS earliest_hire
FROM employees
GROUP BY department_id
ORDER BY avg_salary DESC;

HAVING vs WHERE

SELECT department_id, COUNT(*) AS cnt
FROM   employees
WHERE  active = true       -- filters rows BEFORE grouping
GROUP BY department_id
HAVING COUNT(*) > 5;       -- filters groups AFTER grouping

Use WHERE to filter rows before aggregation. Use HAVING to filter aggregated results. Putting an aggregate in a WHERE clause is a syntax error.

FILTER clause

SELECT
  COUNT(*) FILTER (WHERE status = 'active')   AS active_count,
  COUNT(*) FILTER (WHERE status = 'inactive') AS inactive_count
FROM users;

The FILTER clause lets you compute multiple conditional aggregates in a single query without subqueries. NULLs are ignored by all aggregate functions except COUNT(*).