Home/Learn/SQL/COUNT, SUM, AVG, MIN, MAX Deep Dive

COUNT, SUM, AVG, MIN, MAX Deep Dive

Beginner

Aggregation & Grouping

SQL aggregate functions collapse multiple rows into a single value, but NULL handling, floating-point precision in AVG, and conditional aggregation are frequent sources of subtle bugs.

Overview

COUNT(*) counts all rows including NULLs; COUNT(col) counts only non-NULL values. SUM and AVG silently ignore NULL values — SUM of an all-NULL column returns NULL, not zero. AVG can introduce floating-point imprecision unless you explicitly CAST to NUMERIC. Conditional aggregation — SUM(CASE WHEN ... THEN value END) — pivots row-based data into columns without a secondary query. PostgreSQL extends this with the FILTER clause (SUM(amount) FILTER (WHERE status = 'completed')), which is cleaner but less portable. DISTINCT inside an aggregate (COUNT(DISTINCT col)) removes duplicates before aggregating.

NULL Behaviour in COUNT, SUM, and AVG

NULL is not zero. COUNT(*) includes NULL rows; COUNT(col) skips NULLs. SUM of NULL rows returns NULL. AVG divides by the count of non-NULL values, which can surprise you if most rows are NULL.

SQL — NULL handling in COUNT, SUM, AVG

-- Demonstrate NULL behaviour with orders (some amounts may be NULL)
SELECT
    COUNT(*)             AS total_rows,           -- includes rows where amount IS NULL
    COUNT(amount)        AS non_null_amounts,      -- skips NULL amounts
    COUNT(DISTINCT user_id) AS distinct_users,     -- distinct non-NULL user_ids
    SUM(amount)          AS total_revenue,         -- NULL rows ignored; result is NULL if ALL are NULL
    AVG(amount)          AS avg_amount,            -- divides by COUNT(amount), not COUNT(*)
    COALESCE(SUM(amount), 0) AS safe_total        -- convert NULL result to 0
FROM orders;

-- Gotcha: average differs when NULLs are present
-- If 10 rows exist, 3 have NULL amount, 7 have values:
-- AVG(amount) = SUM of 7 values / 7    (NOT / 10)
-- This may be correct or incorrect depending on business logic

-- AVG precision: use ROUND + CAST for money
SELECT ROUND(AVG(salary::NUMERIC), 2) AS avg_salary FROM employees;

Conditional Aggregation: Pivot Without a Subquery

Conditional aggregation using CASE WHEN or the FILTER clause (PostgreSQL) produces per-category totals in a single pass — equivalent to a pivot table.

SQL — conditional aggregation with CASE and FILTER

-- Revenue by order status in one query (conditional aggregation)

-- Using CASE WHEN (portable, works in all databases):
SELECT
    COUNT(*)                                               AS total_orders,
    SUM(CASE WHEN status = 'completed'  THEN amount END)  AS completed_revenue,
    SUM(CASE WHEN status = 'pending'    THEN amount END)  AS pending_revenue,
    SUM(CASE WHEN status = 'cancelled'  THEN 0 ELSE 0 END) AS cancelled_revenue,
    COUNT(CASE WHEN status = 'completed' THEN 1 END)       AS completed_count
FROM orders;

-- Using FILTER clause (PostgreSQL only — cleaner syntax):
SELECT
    COUNT(*)                                        AS total_orders,
    SUM(amount) FILTER (WHERE status = 'completed') AS completed_revenue,
    SUM(amount) FILTER (WHERE status = 'pending')   AS pending_revenue,
    COUNT(*)    FILTER (WHERE status = 'refunded')  AS refund_count,
    AVG(amount) FILTER (WHERE status = 'completed') AS avg_completed_order
FROM orders;

COUNT(*) vs COUNT(col) NULL Demo and Aggregate DISTINCT

A concrete side-by-side comparison shows the COUNT(*) vs COUNT(col) difference. Aggregate DISTINCT (COUNT DISTINCT, SUM DISTINCT) deduplicates values before aggregating.

SQL — COUNT distinct, SUM distinct, LEFT JOIN aggregation

-- Setup: orders with some NULL product_id values
-- orders: (1, user=1, product=10), (2, user=1, product=NULL), (3, user=2, product=10)

SELECT
    COUNT(*)                AS all_rows,          -- 3
    COUNT(product_id)       AS non_null_products, -- 2 (skips NULL)
    COUNT(DISTINCT product_id) AS distinct_products, -- 1 (only product 10)
    SUM(DISTINCT amount)    AS distinct_amount_sum -- sums only unique amounts
FROM orders;

-- Practical example: unique buyers and total orders per product
SELECT
    p.name,
    COUNT(o.id)              AS total_orders,
    COUNT(DISTINCT o.user_id) AS unique_buyers,   -- deduplicated user count
    SUM(o.amount)            AS total_revenue,
    ROUND(AVG(o.amount)::NUMERIC, 2) AS avg_order_value
FROM products p
LEFT JOIN orders o ON o.product_id = p.id
GROUP BY p.id, p.name
ORDER BY total_revenue DESC NULLS LAST;

Key Points to Remember

1COUNT(*) counts all rows; COUNT(col) counts only non-NULL values — the difference matters when NULLs are present.
2SUM, AVG, MIN, and MAX all ignore NULL values; SUM of an all-NULL set returns NULL, not zero — use COALESCE(SUM(...), 0) defensively.
3AVG divides by the count of non-NULL values, which can be misleading if nullability is meaningful in the domain.
4Conditional aggregation (CASE WHEN or FILTER) pivots row data into columns in a single scan without subqueries.
5COUNT(DISTINCT col) deduplicates before counting but can be slow on large tables — consider HyperLogLog extensions for approximations.
6LEFT JOIN before aggregation preserves products/users with zero orders in the result; INNER JOIN excludes them.

Interview Questions

What is the difference between COUNT(*) and COUNT(column)?

EasyFlipkart

Write a single query that shows total revenue, completed revenue, and cancelled order count for the orders table.

MediumAmazon

Why might AVG(salary) give a different result than SUM(salary)/COUNT(*)?

MediumAdobe

What does COUNT(DISTINCT user_id) do differently from COUNT(user_id)?

EasySwiggy

Ask Aria about COUNT, SUM, AVG, MIN, MAX Deep Dive

Your personal AI tutor — ask anything about this concept

Revision Status

Personal Notes

Discussion

Loading discussion…

Transactions & Savepoints in SQL