Aggregate Functions

The basics

SELECT
  count(*)         AS total_students,
  count(email)     AS with_email,
  avg(cgpa)        AS avg_cgpa,
  max(cgpa)        AS best,
  min(cgpa)        AS worst,
  sum(credits)     AS total_credits,
  round(stddev(cgpa), 3) AS spread
FROM students;

Every aggregate except count(*) skips NULL. So avg(cgpa) divides by the number of non-null values, not by the row count.

Counting

count(*)                  -- every row
count(column)             -- rows where column IS NOT NULL
count(DISTINCT column)    -- distinct non-null values
count(*) FILTER (WHERE cgpa > 3.5)   -- conditional count, Postgres style

count(*) on a large table always scans in Postgres because MVCC makes visibility per-transaction. For an approximate number, read reltuples from pg_class.

Collecting values

SELECT department,
       string_agg(name_en, ', ' ORDER BY name_en) AS students,
       array_agg(id ORDER BY cgpa DESC)           AS ids_by_rank,
       jsonb_agg(jsonb_build_object('name', name_en, 'cgpa', cgpa)) AS json_rows
FROM students
GROUP BY department;

string_agg is the Postgres equivalent of MySQL's GROUP_CONCAT, but with no length limit to raise. jsonb_agg and jsonb_object_agg let you return a nested JSON document straight from SQL, which often removes an entire layer of application code.

FILTER

SELECT
  department,
  count(*)                              AS total,
  count(*) FILTER (WHERE cgpa >= 3.5)   AS excellent,
  avg(cgpa) FILTER (WHERE age < 21)     AS avg_young,
  round(100.0 * count(*) FILTER (WHERE cgpa >= 3.5) / count(*), 1) AS pct_excellent
FROM students
GROUP BY department;

Ordered-set aggregates

SELECT
  department,
  percentile_cont(0.5) WITHIN GROUP (ORDER BY cgpa) AS median_cgpa,
  percentile_disc(0.9) WITHIN GROUP (ORDER BY cgpa) AS p90,
  mode() WITHIN GROUP (ORDER BY grade)              AS most_common_grade
FROM students
GROUP BY department;

percentile_cont interpolates between values; percentile_disc returns an actual value from the data.

Aggregates and WHERE

WHERE runs before grouping, so aggregates are not allowed there:

-- ERROR
SELECT department FROM students WHERE avg(cgpa) > 3.0 GROUP BY department;

-- correct
SELECT department FROM students GROUP BY department HAVING avg(cgpa) > 3.0;

Custom aggregates

Postgres lets you define your own aggregate from a state function — rarely needed, but a reminder of how extensible the system is:

CREATE AGGREGATE product(numeric) (
  SFUNC = numeric_mul,
  STYPE = numeric,
  INITCOND = '1'
);
SELECT product(cgpa) FROM students;