COUNT, SUM, AVG, MIN, MAX

Collapse many rows into one meaningful number — and understand what NULL does to each function

The beer_db has 400 beers. But how many distinct styles? What is the average ABV? Which style has the highest minimum ABV? These questions collapse many rows into one answer — that is what aggregate functions do.\n\nThe critical behaviour: every aggregate function except COUNT() ignores NULLs. If 10 beers have no IBU value, AVG(ibu) averages only the beers that do have one. COUNT() counts all rows. COUNT(ibu) counts only rows where ibu IS NOT NULL.\n\nAggregates are always computed after WHERE filters the rows. You cannot use an aggregate in a WHERE clause — that is what HAVING is for (next lab).

The Five Core Aggregate Functions

Aggregate functions take a set of rows and return a single value.

SELECT
    COUNT(*)                    AS total_beers,
    COUNT(ibu)                  AS beers_with_ibu,
    ROUND(AVG(abv), 2)          AS avg_abv,
    MIN(abv)                    AS lowest_abv,
    MAX(abv)                    AS highest_abv,
    SUM(abv)                    AS sum_abv
FROM beers;

COUNT(*) vs COUNT(column)

COUNT(*) counts every row including those with NULLs. COUNT(column) counts only rows where that column is NOT NULL.

SELECT
    COUNT(*)    AS all_rows,
    COUNT(ibu)  AS rows_with_ibu,     -- skips NULL ibu
    COUNT(description) AS rows_with_desc
FROM beers;

COUNT(DISTINCT …) — Unique Values

-- How many distinct styles exist in the beers table?
SELECT COUNT(DISTINCT style) AS distinct_styles FROM beers;

Filtering Before Aggregating

WHERE runs before the aggregate — it restricts which rows are included.

-- Average ABV of Imperial Stouts only
SELECT ROUND(AVG(abv), 2) AS avg_imperial_stout_abv
FROM beers
WHERE style = 'Imperial Stout';

NULL Propagation Rule

Every aggregate except COUNT(*) silently ignores NULLs. AVG(ibu) divides by the count of non-NULL ibu rows, not by total rows. This means the average can be misleading if many rows have NULL values. Always check COUNT(*) vs COUNT(column) to understand data completeness.

Aggregate function

A function that takes a set of rows and returns a single scalar value. The five core aggregates are COUNT, SUM, AVG, MIN, and MAX. They are evaluated after WHERE filters rows and before ORDER BY sorts the result. When used without GROUP BY, they collapse the entire result set into one row.

NULL in aggregates

Every aggregate function except COUNT() ignores NULL values entirely. AVG(ibu) divides the sum of non-NULL ibu values by the count of non-NULL ibu rows. This means the denominator might be much smaller than the total row count. Always check COUNT() vs COUNT(column) to understand what fraction of rows contributed.

🔌 Connect to beer_db

Connect to beer_db as the beer_db user. All aggregation exercises use the beers and styles tables.

psql -U beer_db -d beer_db

psql (18.4) Type "help" for help. beer_db=>

📊 Your First Aggregate: COUNT(*)

Run a SELECT with all five core aggregate functions at once: COUNT(), COUNT(ibu), ROUND(AVG(abv),2), MIN(abv), MAX(abv). Notice that COUNT() and COUNT(ibu) may differ — that gap is the number of beers with no IBU value.

SELECT COUNT(*) AS total_beers, COUNT(ibu) AS beers_with_ibu, ROUND(AVG(abv), 2) AS avg_abv, MIN(abv) AS min_abv, MAX(abv) AS max_abv FROM beers;

total_beers | beers_with_ibu | avg_abv | min_abv | max_abv -------------+----------------+---------+---------+--------- 400 | 400 | 5.55 | 2.81 | 11.74 (1 row)

🎯 COUNT(DISTINCT …) — Unique Styles

Count how many distinct beer styles exist in the beers table. Use COUNT(DISTINCT style) — without DISTINCT you would just get the total row count again.

SELECT COUNT(DISTINCT style) AS distinct_styles FROM beers;

distinct_styles ----------------- 25 (1 row)

🔍 Filter Before Aggregating

Find the average, minimum, and maximum ABV for Imperial Stout beers only. The WHERE clause runs before the aggregate — only matching rows are included in the calculation.

SELECT COUNT(*) AS imperial_stout_count, ROUND(AVG(abv),2) AS avg_abv, MIN(abv) AS min_abv, MAX(abv) AS max_abv FROM beers WHERE style = 'Imperial Stout';

imperial_stout_count | avg_abv | min_abv | max_abv ----------------------+---------+---------+--------- 11 | 9.80 | 8.08 | 11.74 (1 row)

📏 MIN and MAX on Text

MIN and MAX work on text columns too — they return the lexicographically first and last values. Find the alphabetically first and last beer names in the table.

SELECT MIN(name) AS first_alphabetically, MAX(name) AS last_alphabetically FROM beers;

first_alphabetically | last_alphabetically ----------------------+--------------------- Anniversary Ale | Wild Weizen (1 row)

🍺 SUM of ABV — Aggregate a Numeric Total

Find the total SUM of all ABV values, the total number of beers, and use them to manually verify the AVG (SUM/COUNT should equal AVG). This builds intuition for what AVG is actually computing.

SELECT SUM(abv) AS sum_abv, COUNT(*) AS beer_count, ROUND(SUM(abv) / COUNT(*), 2) AS manual_avg, ROUND(AVG(abv), 2) AS pg_avg FROM beers;

sum_abv | beer_count | manual_avg | pg_avg ---------+------------+------------+-------- 2221.80 | 400 | 5.55 | 5.55 (1 row)

Lab complete! You can now summarise any dataset with aggregate functions:\n\n\n COUNT(*) : ✅ all rows including NULLs\n COUNT(col) : ✅ non-NULL rows only\n COUNT(DISTINCT col) : ✅ unique non-NULL values\n AVG / SUM / MIN / MAX : ✅ all skip NULLs silently\n WHERE before aggregate: ✅ filters which rows are summarised\n

Enable JavaScript to run the live terminal and track your progress.