Chapter 08 · Aggregation and Grouping
COUNT, SUM, AVG, MIN, and MAX
Aggregate functions compress many input rows into summary values, but their treatment of NULLs, empty inputs, and data types must be understood precisely.
Learning outcomes
An aggregate function consumes a set of input values and returns one summary value. Correct use depends on knowing what counts as an input value, how NULL is treated, and what happens when no rows qualify.
Distinguish COUNT(*) from COUNT(expression) and COUNT(DISTINCT expression).
Use SUM, AVG, MIN, and MAX with numeric and comparable values.
Predict aggregate results when inputs contain NULL or no rows.
Calculate weighted averages and derived totals without averaging at the wrong grain.
Five core aggregates
| Function | Question answered | NULL behavior |
|---|---|---|
COUNT(*) | How many rows are in the input? | Counts rows even when every selected column is NULL. |
COUNT(expr) | How many non-NULL expression values exist? | Ignores rows where the expression is NULL. |
SUM(expr) | What is the arithmetic total? | Ignores NULL; returns NULL for an empty input in standard behavior. |
AVG(expr) | What is the mean of non-NULL values? | Equivalent conceptually to SUM divided by non-NULL count. |
MIN/MAX(expr) | What are the smallest and largest comparable values? | Ignore NULL values. |
Count rows, values, and distinct values
SELECT COUNT(*) AS sale_rows, COUNT(discount_pct) AS sales_with_discount, COUNT(DISTINCT customer_id) AS active_customersFROM sale;The first count sees all ten rows. The second counts only rows with a known discount. The third counts unique participating customer IDs, not customer rows in the customer table.
Use COUNT(*) when the requirement is “number of rows.” Use COUNT(column) only when NULL means the value should not be counted.
Totals and extrema
SELECT SUM(quantity) AS total_units, AVG(quantity) AS average_units_per_sale, MIN(quantity) AS smallest_order, MAX(quantity) AS largest_order, MIN(sold_at) AS first_sale_at, MAX(sold_at) AS latest_sale_atFROM sale;ISO-formatted timestamps sort chronologically as text. That property is a storage convention, not a general guarantee for arbitrary date strings.
Aggregate a derived expression
SELECT ROUND(SUM(p.unit_price * s.quantity), 2) AS gross_revenue, ROUND(SUM( p.unit_price * s.quantity * (1 - COALESCE(s.discount_pct, 0) / 100.0) ), 2) AS net_revenueFROM sale AS sJOIN product AS p ON p.product_id = s.product_idWHERE s.status <> 'refunded';The expression is evaluated per joined sale row; SUM then combines those row-level amounts. The 100.0 literal forces fractional arithmetic.
AVG uses non-NULL inputs
SELECT AVG(discount_pct) AS average_known_discount, SUM(discount_pct) * 1.0 / COUNT(discount_pct) AS same_definitionFROM sale;Unknown discounts are absent from both the numerator and denominator. Replacing NULL with zero answers a different question: “average discount if missing means no discount.”
SELECT AVG(COALESCE(discount_pct, 0)) AS average_with_missing_as_zeroFROM sale;Weighted averages
When observations represent different quantities, an ordinary average of unit prices gives each sale row equal weight. A per-unit average must weight price by quantity.
SELECT ROUND( SUM(p.unit_price * s.quantity) / SUM(s.quantity), 2 ) AS weighted_average_unit_priceFROM sale AS sJOIN product AS p ON p.product_id = s.product_id;Empty input behavior
SELECT COUNT(*) AS row_count, SUM(quantity) AS total_units, AVG(quantity) AS average_units, MIN(quantity) AS minimum_units, MAX(quantity) AS maximum_unitsFROM saleWHERE sale_id < 0;COUNT(*) returns zero. The other aggregates return NULL because there is no value from which to derive a total, mean, minimum, or maximum. Use COALESCE only when the report contract requires a numeric zero.
Practice database
Run this setup once in a disposable SQLite database. Every Chapter 8 example uses the same customer, product, and sale facts so changes in result grain remain easy to compare.
PRAGMA foreign_keys = ON;DROP TABLE IF EXISTS sale;DROP TABLE IF EXISTS product;DROP TABLE IF EXISTS customer;CREATE TABLE customer ( customer_id INTEGER PRIMARY KEY, full_name TEXT NOT NULL, city TEXT, segment TEXT NOT NULL CHECK (segment IN ('consumer', 'business'))) STRICT;CREATE TABLE product ( product_id INTEGER PRIMARY KEY, product_name TEXT NOT NULL, category TEXT NOT NULL, unit_price REAL NOT NULL CHECK (unit_price >= 0)) STRICT;CREATE TABLE sale ( sale_id INTEGER PRIMARY KEY, customer_id INTEGER NOT NULL REFERENCES customer(customer_id), product_id INTEGER NOT NULL REFERENCES product(product_id), quantity INTEGER NOT NULL CHECK (quantity > 0), discount_pct REAL, sold_at TEXT NOT NULL, channel TEXT NOT NULL CHECK (channel IN ('web', 'partner', 'direct')), status TEXT NOT NULL CHECK (status IN ('paid', 'pending', 'refunded')), CHECK (discount_pct IS NULL OR discount_pct BETWEEN 0 AND 100)) STRICT;INSERT INTO customer VALUES (1, 'Nadia Rahimi', 'Tehran', 'consumer'), (2, 'Omar Haddad', 'Berlin', 'business'), (3, 'Lina Chen', NULL, 'consumer'), (4, 'Ava Morgan', 'Berlin', 'consumer'), (5, 'Noah Silva', 'Lisbon', 'business'), (6, 'Marta Costa', NULL, 'business');INSERT INTO product VALUES (10, 'Database Foundations', 'course', 49.00), (11, 'SQL Query Practice', 'course', 69.00), (12, 'SQL Reference Card', 'book', 15.00), (13, 'SQLite Lab Bundle', 'lab', 29.00), (14, 'Data Quality Workbook', 'book', 24.50), (15, 'Database Operations', 'course', 89.00);INSERT INTO sale VALUES (100, 1, 10, 1, NULL, '2026-08-01 09:15:00', 'web', 'paid'), (101, 2, 11, 3, 10, '2026-08-01 10:45:00', 'direct', 'paid'), (102, 1, 12, 2, 5, '2026-08-02 11:30:00', 'web', 'paid'), (103, 4, 10, 1, NULL, '2026-08-03 13:05:00', 'partner', 'pending'), (104, 2, 13, 2, 15, '2026-08-03 15:20:00', 'direct', 'paid'), (105, 5, 15, 1, NULL, '2026-08-04 16:40:00', 'partner', 'paid'), (106, 3, 14, 4, 20, '2026-08-05 08:10:00', 'web', 'refunded'), (107, 5, 12, 5, NULL, '2026-08-05 12:00:00', 'web', 'paid'), (108, 2, 15, 1, 10, '2026-08-06 09:25:00', 'direct', 'pending'), (109, 4, 13, 3, NULL, '2026-08-06 14:50:00', 'partner', 'paid');The data includes repeated cities, customers with no sales, nullable discounts, three sales statuses, and multiple channels. Those variations make aggregate behavior visible.
Practice lab
- Count all sales, paid sales, and distinct customers with sales.
- Calculate total units and total gross value.
- Compare average quantity per sale with average unit price weighted by quantity.
- Find the earliest and latest paid sale.
- Return zero rather than NULL for the total of an impossible filter.
SELECT COUNT(*) AS all_sales, SUM(status = 'paid') AS paid_sales, COUNT(DISTINCT customer_id) AS active_customers, SUM(quantity) AS total_units, ROUND(COALESCE(SUM(quantity) FILTER (WHERE status = 'cancelled'), 0), 2) AS cancelled_unitsFROM sale;Checkpoint
Choose the correct aggregate
- Why can COUNT(*) and COUNT(discount_pct) return different values?
- What does AVG(discount_pct) use as its denominator?
- Why is an average of sale-level prices not necessarily a per-unit average?
- What does SUM return over an empty input?
- When should COALESCE convert that result to zero?
Review the answers
COUNT(*) counts rows; COUNT(expression) counts non-NULL values. AVG divides by the non-NULL count. Weighted observations require weighted arithmetic. SUM over no rows returns NULL. Convert it to zero only when the reporting meaning defines absence as zero.
Summary and references
- COUNT(*) counts rows; COUNT(expr) counts known values.
- Most aggregates ignore NULL inputs.
- Empty inputs return NULL except for COUNT.
- Derived expressions are computed before aggregation.
- Weighted averages must preserve the correct denominator.