33 COUNT, SUM, AVG

Aggregate functions compute a single value across many rows. The five most common — count, sum, avg, min, max — are foundational. They behave a little differently with NULL: count(*) counts every row including those with NULLs; count(col) counts non-NULL values of col; sum/avg/min/max ignore NULLs entirely. There are dozens more (stddev, string_agg, array_agg, bool_and, bool_or, jsonb_agg, …), with behavior specific to each aggregate: array_agg and jsonb_agg, for example, retain NULL inputs.

What you’ll learn

• count(*) vs count(col) vs count(DISTINCT col)
• sum, avg, min, max on numeric and other types
• How aggregates handle NULL
• string_agg and array_agg for building lists
• FILTER (WHERE ...) for per-aggregate conditions

Example

-- Basic counts
SELECT count(*) AS total_rows FROM fruits;
SELECT count(name) AS non_null_names FROM fruits;
SELECT count(DISTINCT name) AS unique_names FROM fruits;

-- Sum and average
SELECT sum(id) AS sum_id, avg(id) AS avg_id, round(avg(id), 2) AS avg_round
FROM fruits;

-- Min and max work on numbers, text, dates
SELECT min(name) AS min_name, max(name) AS max_name FROM fruits;

-- Build a string or array from a column
SELECT string_agg(name, ', ' ORDER BY id) AS names_csv,
       array_agg(name ORDER BY id)         AS names_arr
FROM fruits;

-- FILTER: per-aggregate WHERE; replaces messy CASE WHEN tricks
SELECT count(*) AS total,
       count(*) FILTER (WHERE id <= 3) AS low,
       count(*) FILTER (WHERE id > 3)  AS high,
       sum(id)  FILTER (WHERE id > 3)  AS high_sum
FROM fruits;

count(*) is the row count — fast and clear. count(col) skips NULL values of col and is the right call when you want “how many rows have a non-NULL email?” count(DISTINCT col) deduplicates first; it is slower because it must materialize the distinct set.

sum returns the sum, or NULL if there are no rows (not 0 — surprising, but standard). avg returns NULL on no rows; on integer inputs, avg returns a numeric because the average of integers is rarely an integer. min and max work on anything orderable, including text and timestamps.

string_agg and array_agg are PostgreSQL’s “concatenate the values” aggregates — exactly the right tool when you want to render a list. The optional ORDER BY inside controls the order of concatenation, which the default unordered behavior cannot guarantee.

FILTER (WHERE ...) is the SQL-standard way to apply a per-aggregate condition. It replaces the older sum(CASE WHEN cond THEN x END) idiom — same result, much clearer.

Run the example

To run:

$ psql -f source/count-sum-avg.sql postgres
 total_rows
------------
          8
(1 row)
 ...
   names_csv
-----------------------------------------------------------
 apricot, blueberry, chestnut, damson, elderberry, fruit_1, fruit_2, fruit_3
(1 row)

 total | low | high | high_sum
-------+-----+------+----------
     8 |   3 |    5 |      315
(1 row)

Common pitfalls

• sum of zero rows returns NULL, not 0. Use COALESCE(sum(x), 0) if you need 0.
• avg on an integer column produces a numeric result; if you want a rounded display, wrap in round.
• count(col) skipping NULLs is a feature, but it surprises beginners. count(*) is the row count; everything else is “non-null count.”
• Aggregates without GROUP BY collapse the entire result to one row. With GROUP BY you get one row per group (next lesson).

Downloads

SQL file: count-sum-avg.sql · All lesson files · Setup guide