Module F-4·18 min read

COUNT, SUM, AVG, GROUP BY, HAVING — the mental model that confuses beginners, explained from scratch.

F-4 — Aggregation — Summarising Your Data

Who this module is for: You completed F-3 and can filter and sort individual rows. Now you need to answer questions about groups of rows — "how many products are in each category?", "what is the average price?", "which categories have more than 5 products?". This module covers aggregation — the most conceptually tricky part of SQL for beginners.

The Problem Aggregation Solves

So far, every query has returned rows from the table — one row of output per row of input (or fewer after filtering). Aggregation is different: it collapses multiple rows into a single summary value.

text

Before aggregation (6 rows, 6 output rows):
  Keyboard     | peripherals | 129.99
  Mouse        | peripherals |  49.99
  Monitor Stand| accessories |  39.99
  USB-C Hub    | accessories |  64.99
  Laptop Sleeve| accessories |  24.99
  Webcam       | peripherals |  89.99

After aggregation (collapse to summary per category, 2 output rows):
  peripherals | 3 products | avg $89.99
  accessories | 3 products | avg $43.32

Aggregate Functions

These functions take a column of values and return a single summary value:

sql

-- COUNT: how many rows?
SELECT COUNT(*) FROM products;               -- total rows
SELECT COUNT(description) FROM products;     -- rows where description is NOT NULL
SELECT COUNT(DISTINCT category) FROM products; -- unique values

-- SUM: total of a numeric column
SELECT SUM(price) FROM products;

-- AVG: average value
SELECT AVG(price) FROM products;

-- MIN / MAX: smallest or largest value
SELECT MIN(price) FROM products;
SELECT MAX(price) FROM products;

-- ROUND: clean up decimal output
SELECT ROUND(AVG(price), 2) FROM products;  -- round to 2 decimal places

When you use an aggregate function without GROUP BY, it collapses the entire table into a single row:

sql

SELECT
  COUNT(*)           AS total_products,
  ROUND(AVG(price), 2) AS avg_price,
  MIN(price)         AS cheapest,
  MAX(price)         AS most_expensive,
  SUM(price)         AS total_value
FROM products;

--  total_products | avg_price | cheapest | most_expensive | total_value
-- ----------------+-----------+----------+----------------+-------------
--               5 |     74.99 |    39.99 |         129.99 |      374.95

`GROUP BY` — The Mental Model

GROUP BY is where most beginners get confused. The mental model:

PostgreSQL divides all your rows into groups based on the column(s) you specify
Each group is collapsed into a single output row
You can then apply aggregate functions to each group separately

sql

-- "How many products are in each category?"
SELECT category, COUNT(*) AS product_count
FROM products
GROUP BY category;

--    category   | product_count
-- --------------+---------------
--  accessories  |             3
--  peripherals  |             3

Visualise what happens:

text

Full table:
  Keyboard      | peripherals          ─┐
  Mouse         | peripherals           ├─ GROUP 1: peripherals (3 rows)
  Webcam        | peripherals          ─┘
  Monitor Stand | accessories          ─┐
  USB-C Hub     | accessories           ├─ GROUP 2: accessories (3 rows)
  Laptop Sleeve | accessories          ─┘

After GROUP BY category + COUNT(*):
  peripherals  | 3
  accessories  | 3

The Golden Rule of GROUP BY

Every column in your SELECT list must either be in the GROUP BY clause OR be wrapped in an aggregate function.

sql

-- ✅ CORRECT: category is in GROUP BY, COUNT(*) is an aggregate
SELECT category, COUNT(*) FROM products GROUP BY category;

-- ✅ CORRECT: category in GROUP BY, AVG is an aggregate
SELECT category, AVG(price) FROM products GROUP BY category;

-- ❌ ERROR: name is neither in GROUP BY nor an aggregate
SELECT category, name, COUNT(*) FROM products GROUP BY category;
-- ERROR: column "products.name" must appear in the GROUP BY clause
-- or be used in an aggregate function

This error trips up beginners constantly. The reason: if you group by category, and there are 3 rows in the "peripherals" group, which name value should PostgreSQL return? There are three of them. It cannot pick one arbitrarily — so it forces you to either group by name too (making each row unique) or use an aggregate.

Multiple columns in GROUP BY

sql

-- "How many in-stock vs out-of-stock products per category?"
SELECT category, in_stock, COUNT(*) AS count
FROM products
GROUP BY category, in_stock
ORDER BY category, in_stock;

--    category   | in_stock | count
-- --------------+----------+-------
--  accessories  | f        |     1
--  accessories  | t        |     2
--  peripherals  | t        |     3

When you GROUP BY multiple columns, each unique combination of those columns becomes one output row.

Common Aggregation Patterns

sql

-- Total value of inventory by category
SELECT
  category,
  COUNT(*) AS num_products,
  SUM(price) AS total_value,
  ROUND(AVG(price), 2) AS avg_price
FROM products
GROUP BY category
ORDER BY total_value DESC;

-- Cheapest and most expensive per category
SELECT
  category,
  MIN(price) AS cheapest,
  MAX(price) AS most_expensive,
  MAX(price) - MIN(price) AS price_range
FROM products
GROUP BY category;

-- Count in-stock vs out-of-stock across the whole catalog
SELECT
  in_stock,
  COUNT(*) AS count
FROM products
GROUP BY in_stock;

`HAVING` — Filtering Groups

WHERE filters individual rows before grouping. HAVING filters groups after aggregation.

sql

-- Categories with more than 2 products
SELECT category, COUNT(*) AS product_count
FROM products
GROUP BY category
HAVING COUNT(*) > 2;

-- Categories where average price is above $50
SELECT category, ROUND(AVG(price), 2) AS avg_price
FROM products
GROUP BY category
HAVING AVG(price) > 50;

The difference between WHERE and HAVING:

sql

-- WHERE: filter rows BEFORE grouping
-- "Count products per category, but only count products priced over $30"
SELECT category, COUNT(*) AS count
FROM products
WHERE price > 30          -- remove cheap products before counting
GROUP BY category;

-- HAVING: filter groups AFTER grouping
-- "Only show categories where the count is greater than 2"
SELECT category, COUNT(*) AS count
FROM products
GROUP BY category
HAVING COUNT(*) > 2;      -- remove groups with few products

-- Combined: WHERE filters rows, HAVING filters the resulting groups
SELECT category, COUNT(*) AS count
FROM products
WHERE price > 30          -- first: only count products over $30
GROUP BY category
HAVING COUNT(*) > 1;      -- then: only show categories with more than 1 qualifying product

The Execution Order

Understanding the order PostgreSQL executes a query helps predict how WHERE vs HAVING behaves:

text

1. FROM     — identify the table
2. WHERE    — filter individual rows
3. GROUP BY — divide remaining rows into groups
4. HAVING   — filter groups
5. SELECT   — compute output columns
6. ORDER BY — sort the result
7. LIMIT    — return only N rows

This is why you cannot use a column alias from SELECT in a WHERE clause — WHERE runs before SELECT:

sql

-- ❌ ERROR: 'avg_price' alias is not available in WHERE
SELECT category, AVG(price) AS avg_price
FROM products
WHERE avg_price > 50   -- ERROR: column "avg_price" does not exist
GROUP BY category;

-- ✅ CORRECT: use HAVING (which runs after SELECT-level computation)
-- Or repeat the expression:
SELECT category, AVG(price) AS avg_price
FROM products
GROUP BY category
HAVING AVG(price) > 50;

`COUNT(*)` vs `COUNT(column)` vs `COUNT(DISTINCT column)`

These three are different:

sql

-- Setup: add a product with NULL description
INSERT INTO products (name, price) VALUES ('Mystery Box', 19.99);
-- This row has description = NULL

-- COUNT(*): counts every row, including those with NULLs
SELECT COUNT(*) FROM products;               -- 7 (includes Mystery Box)

-- COUNT(column): counts only rows where the column is NOT NULL
SELECT COUNT(description) FROM products;     -- 6 (excludes Mystery Box)

-- COUNT(DISTINCT column): counts unique non-NULL values
SELECT COUNT(DISTINCT category) FROM products;  -- 2 (peripherals, accessories)
-- Note: general category from the Mystery Box is also NULL, doesn't count

`COALESCE` and `NULLIF` in Aggregations

NULL values are silently ignored by aggregate functions — but this can surprise you:

sql

-- AVG ignores NULL values (doesn't count them in the denominator)
SELECT AVG(description) FROM products;   -- ignores rows where description is NULL

-- Replace NULL with a default value using COALESCE
SELECT
  name,
  COALESCE(description, 'No description provided') AS description
FROM products;

-- NULLIF: return NULL if two values are equal (useful to avoid division by zero)
SELECT
  category,
  SUM(price) / NULLIF(COUNT(*), 0) AS manual_avg
FROM products
GROUP BY category;
-- Without NULLIF: if somehow COUNT(*) were 0, you'd get a division by zero error
-- With NULLIF: returns NULL instead

Practical Exercise: Sales Analysis

Create a sales table and practice aggregation:

sql

CREATE TABLE sales (
  id          BIGSERIAL    PRIMARY KEY,
  product     TEXT         NOT NULL,
  region      TEXT         NOT NULL,
  quantity    INTEGER      NOT NULL,
  unit_price  NUMERIC(10,2) NOT NULL,
  sale_date   DATE         NOT NULL
);

INSERT INTO sales (product, region, quantity, unit_price, sale_date) VALUES
  ('Keyboard', 'North',   2, 129.99, '2026-01-15'),
  ('Mouse',    'North',   5,  49.99, '2026-01-15'),
  ('Keyboard', 'South',   1, 129.99, '2026-01-16'),
  ('Webcam',   'North',   3,  89.99, '2026-01-17'),
  ('Mouse',    'South',   4,  49.99, '2026-02-01'),
  ('Keyboard', 'East',    2, 129.99, '2026-02-03'),
  ('Webcam',   'South',   1,  89.99, '2026-02-10'),
  ('Mouse',    'East',    6,  49.99, '2026-02-15'),
  ('Keyboard', 'North',   3, 129.99, '2026-03-01'),
  ('Webcam',   'East',    2,  89.99, '2026-03-05');

Now answer these questions:

sql

-- Q1: Total revenue across all sales
SELECT SUM(quantity * unit_price) AS total_revenue FROM sales;

-- Q2: Total revenue by product, highest first
SELECT
  product,
  SUM(quantity * unit_price) AS revenue,
  SUM(quantity) AS units_sold
FROM sales
GROUP BY product
ORDER BY revenue DESC;

-- Q3: Total revenue by region
SELECT
  region,
  SUM(quantity * unit_price) AS revenue
FROM sales
GROUP BY region
ORDER BY revenue DESC;

-- Q4: Average order value per region
SELECT
  region,
  ROUND(AVG(quantity * unit_price), 2) AS avg_order_value,
  COUNT(*) AS num_orders
FROM sales
GROUP BY region;

-- Q5: Products that sold more than 5 units in total
SELECT product, SUM(quantity) AS total_units
FROM sales
GROUP BY product
HAVING SUM(quantity) > 5
ORDER BY total_units DESC;

-- Q6: Monthly revenue (group by year-month)
SELECT
  DATE_TRUNC('month', sale_date) AS month,
  SUM(quantity * unit_price) AS revenue
FROM sales
GROUP BY DATE_TRUNC('month', sale_date)
ORDER BY month;

-- Q7: Best-selling product per region
SELECT
  region,
  product,
  SUM(quantity) AS units
FROM sales
GROUP BY region, product
ORDER BY region, units DESC;

Summary

Concept	What it does
`COUNT(*)`	Count all rows
`COUNT(col)`	Count non-NULL values in column
`COUNT(DISTINCT col)`	Count unique non-NULL values
`SUM(col)`	Sum all values
`AVG(col)`	Average of all non-NULL values
`MIN(col)`	Smallest value
`MAX(col)`	Largest value
`GROUP BY col`	Collapse rows with same col value into one group
`HAVING condition`	Filter groups after aggregation
`WHERE` vs `HAVING`	WHERE filters rows before grouping; HAVING filters groups after
`COALESCE(val, default)`	Replace NULL with a default
`NULLIF(a, b)`	Return NULL if a equals b

The rule that saves beginners from errors: every non-aggregated column in SELECT must be in GROUP BY.

Module F-5 covers joins — the feature that makes relational databases powerful by connecting data spread across multiple tables.

Next: F-5 — Connecting Tables — Joins Demystified →

Knowledge Check

Question 1: A junior engineer writes the following SQL query to find the average price of products in each category, along with the name of one product from that category:

sql

SELECT category, name, AVG(price) AS avg_category_price
FROM products
GROUP BY category;

This query results in an error: ERROR: column "products.name" must appear in the GROUP BY clause or be used in an aggregate function. As a Senior Principal Software Engineer, what is the most accurate explanation for this error, highlighting the underlying architectural principle?

A) The name column cannot be included because AVG(price) is an aggregate, and you cannot mix aggregated and non-aggregated columns in the SELECT list.
B) PostgreSQL cannot arbitrarily choose a single name value for a group that contains multiple products, thus enforcing the "Golden Rule" that non-aggregated SELECT columns must be part of the GROUP BY clause.
C) The name column is a TEXT type, and GROUP BY clauses only support numeric or date types for grouping, leading to a type mismatch error.
D) The AVG() function implicitly requires all other SELECT list columns to also be aggregated, which name is not.

Reveal Answer

Correct Answer: B

B is correct because when you GROUP BY category, there can be multiple name values within a single category group. PostgreSQL cannot decide which name to display for that aggregated category row. The "Golden Rule" of GROUP BY dictates that any column in the SELECT list that is not part of an aggregate function must also be included in the GROUP BY clause to ensure a one-to-one mapping between the grouped output row and the non-aggregated column. Option A is incorrect because you can mix aggregated and non-aggregated columns, as long as the non-aggregated ones are in the GROUP BY. Option C is incorrect; GROUP BY works with TEXT types. Option D is incorrect; AVG() does not implicitly require other columns to be aggregated; it's about the non-aggregated columns needing to be part of the grouping key.

Question 2: A database administrator needs to identify product categories that meet two criteria:

They must contain at least 5 products.
Among those products, only products with a price greater than $50 should be considered for the count.

Which of the following SQL queries correctly implements these requirements, considering the PostgreSQL query execution order?

A) SELECT category, COUNT() AS product_count FROM products HAVING COUNT() > 4 AND price > 50 GROUP BY category;
B) SELECT category, COUNT() AS product_count FROM products WHERE price > 50 GROUP BY category HAVING COUNT() > 4;
C) SELECT category, COUNT() AS product_count FROM products GROUP BY category WHERE price > 50 HAVING COUNT() > 4;
D) SELECT category, COUNT() AS product_count FROM products WHERE COUNT() > 4 GROUP BY category HAVING price > 50;

Reveal Answer

Correct Answer: B

B is correct. The requirement "only products with a price greater than $50 should be considered for the count" implies a row-level filter that must occur before the aggregation. This is handled by the WHERE price > 50 clause. The requirement "They must contain at least 5 products" (after the price filter) is a group-level filter, which must occur after the GROUP BY and aggregation. This is handled by HAVING COUNT(*) > 4. Option A is incorrect because price > 50 is a row-level condition and cannot be used in HAVING. Option C is incorrect because WHERE must come before GROUP BY in the query execution order. Option D is incorrect because COUNT(*) is an aggregate and cannot be used in WHERE, and price is a row-level column that cannot be used in HAVING without being aggregated. The correct execution order is FROM -> WHERE -> GROUP BY -> HAVING -> SELECT -> ORDER BY -> LIMIT.

Question 3: Consider a products table with the following data, including a NULL value for description for 'Mystery Box' and a NULL for category for 'Uncategorized Item':

name	category	description	price
Keyboard	peripherals	Mechanical	129.99
Mouse	peripherals	Wireless	49.99
Monitor Stand	accessories	Ergonomic	39.99
USB-C Hub	accessories	7-in-1	64.99
Laptop Sleeve	accessories	Neoprene	24.99
Mystery Box	peripherals	NULL	19.99
Uncategorized Item	NULL	Generic	5.00

A developer runs the following three queries:

SELECT COUNT(*) FROM products;
SELECT COUNT(description) FROM products;
SELECT COUNT(DISTINCT category) FROM products;

What will be the respective outputs for these queries, and what is the key implication for data quality and reporting in a production environment?

A) 1: 7, 2: 6, 3: 2. Implication: COUNT(column) and COUNT(DISTINCT column) silently ignore NULL values, which can lead to undercounting if NULLs represent valid, but unspecified, data points.
B) 1: 7, 2: 7, 3: 3. Implication: All COUNT functions treat NULLs as distinct values, which can inflate counts of unique items.
C) 1: 5, 2: 5, 3: 2. Implication: COUNT functions only consider rows with complete data, potentially hiding incomplete records.
D) 1: 7, 2: 6, 3: 3. Implication: COUNT(*) includes NULLs, while COUNT(column) and COUNT(DISTINCT column) count NULLs as a single distinct value.

Reveal Answer

Correct Answer: A

A is correct.

COUNT(*) counts all rows, including those with NULLs in any column. There are 7 rows in total, so COUNT(*) returns 7.
COUNT(description) counts only rows where the description column is not NULL. The 'Mystery Box' has a NULL description, so it's excluded. This results in 6.
COUNT(DISTINCT category) counts unique non-NULL values in the category column. The categories are 'peripherals', 'accessories', and NULL. NULL is ignored by COUNT(DISTINCT), so only 'peripherals' and 'accessories' are counted, resulting in 2. The key implication is that COUNT(column) and COUNT(DISTINCT column) silently ignore NULL values. In a production environment, this behavior is crucial to understand because if NULL represents a meaningful "unknown" or "not applicable" state, these counts might underrepresent the true number of items or unique categories if NULLs should have been considered. Developers must explicitly handle NULLs (e.g., with COALESCE) if they need them to be included in counts or treated as distinct values. Options B, C, and D misrepresent how NULLs are handled by one or more of the COUNT functions.

PreviousModule F-3: Filtering, Sorting, and Finding What You Need Next Module F-5: Connecting Tables — Joins Demystified

Discussion

Join the discussion

Loading comments...

F-4 — Aggregation — Summarising Your Data

The Problem Aggregation Solves

Aggregate Functions

GROUP BY — The Mental Model

The Golden Rule of GROUP BY

Multiple columns in GROUP BY

Common Aggregation Patterns

HAVING — Filtering Groups

The Execution Order

COUNT(*) vs COUNT(column) vs COUNT(DISTINCT column)

COALESCE and NULLIF in Aggregations

Practical Exercise: Sales Analysis

Summary

Discussion

`GROUP BY` — The Mental Model

`HAVING` — Filtering Groups

`COUNT(*)` vs `COUNT(column)` vs `COUNT(DISTINCT column)`

`COALESCE` and `NULLIF` in Aggregations