SQL › Counting and Totaling

COUNT, SUM, AVG, MIN and MAX

Aggregate functions turn many rows into one value: how many, how much in total, the average, the smallest and the largest.

Syntax

Order of execution: FROM (and joins) → WHERE → aggregates → SELECT → ORDER BY

WHERE picks the rows first; the aggregates then summarise the rows that are left.

Code
SELECT COUNT(*), COUNT(column), COUNT(DISTINCT column),
       SUM(column), AVG(column), MIN(column), MAX(column),
       COUNT(*) FILTER (WHERE condition)
FROM table_name
WHERE condition;

Examples

A whole table in one row

Every aggregate looks at all the rows that reach it.

Code
SELECT COUNT(*) AS orders, SUM(total_amount) AS revenue,
       MIN(order_date) AS first_order, MAX(order_date) AS last_order
FROM orders;

Counting rows, values and different values

COUNT(column) skips NULLs, and DISTINCT counts each value once.

Code
SELECT COUNT(*) AS customers,              -- every customer
       COUNT(1) AS also_customers,         -- COUNT(1) counts rows too, the same as COUNT(*)
       COUNT(phone) AS with_phone,         -- customers whose phone isn't NULL
       COUNT(DISTINCT city) AS cities      -- each city once
FROM customers;

NULLs are skipped

SUM, AVG, MIN and MAX ignore NULLs too; they don't treat them as 0.

Code
SELECT MIN(date_of_birth) AS oldest_birthday, COUNT(date_of_birth) AS known_birthdays
FROM customers;

Filter first, then summarise

WHERE decides which rows are counted.

Code
SELECT COUNT(*) AS payments, SUM(amount) AS collected
FROM payments
WHERE method = 'upi' AND status = 'success';

Counting only some rows

FILTER gives several counts from one pass over the table. It's standard SQL, but MySQL, SQL Server and Oracle don't have it: they put a CASE inside the function instead.

Code
SELECT COUNT(*) AS payments,
       COUNT(*) FILTER (WHERE status = 'success') AS succeeded,
       COUNT(*) FILTER (WHERE status = 'failed') AS failed
FROM payments;

Aggregates over a join

Join first, then summarise the joined rows.

Code
SELECT COUNT(*) AS book_reviews, ROUND(AVG(r.rating), 2) AS avg_stars
FROM reviews r
JOIN products p ON p.product_id = r.product_id
WHERE p.category_id = 2;

Percentages and whole numbers

In PostgreSQL and SQL Server, a whole number divided by a whole number drops the decimals (MySQL and Oracle keep them). Multiply by 100.0 first, which works everywhere.

Code
SELECT 7 / 2 AS whole, 7 / 2.0 AS exact;    -- 3 and 3.5
 
SELECT ROUND(100.0 * COUNT(*) FILTER (WHERE method = 'cod') / COUNT(*), 1) AS cod_pct
FROM payments;

Median and percentiles

The median is the middle value once the values are sorted: half are below it, half above. A few very large values pull the average up, but barely move the median.

PERCENTILE_CONT(0.5) WITHIN GROUP (ORDER BY column) gives the median, and 0.9 gives the value 90% of rows are below. With an even count, it averages the two middle values; PERCENTILE_DISC returns one of them instead, a value that really occurs.

PostgreSQL and Oracle write it this way. SQL Server has it only as a window function, with OVER () at the end, and MySQL has neither.

Code
-- Products customers can buy: a few expensive gadgets pull the average up, not the median
SELECT ROUND(AVG(price), 2) AS avg_price,
       PERCENTILE_CONT(0.5) WITHIN GROUP (ORDER BY price) AS median_price,     -- between the two middle prices
       PERCENTILE_DISC(0.5) WITHIN GROUP (ORDER BY price) AS middle_listed     -- the lower of the two
FROM products
WHERE is_available;
 
-- In PostgreSQL the result is a floating-point number: cast it to NUMERIC before ROUND
SELECT ROUND(PERCENTILE_CONT(0.9) WITHIN GROUP (ORDER BY price)::numeric, 2) AS price_90th
FROM products;

Aggregates with plain columns

One summary row can't also show a value from each row.

Code
-- Fails: city isn't summarised (that needs GROUP BY, the next lesson)
SELECT city, COUNT(*) FROM customers;

Remember

• COUNT(*) counts rows; COUNT(column) skips NULLs.
• SUM, AVG, MIN and MAX ignore NULLs, so AVG is the average of the known values.
• Use COUNT(DISTINCT column) for "how many different".
• In PostgreSQL, whole number ÷ whole number drops the decimals: write 100.0.
• The median is PERCENTILE_CONT(0.5) WITHIN GROUP (ORDER BY column), not AVG.

Practice

1. The Catalogue at a Glance (Easy)
2. Toys Sales Summary (Easy)
3. Cancellation Rate in 2025 (Easy)
4. Median vs Average Order Value (Medium)