Aggregate Functions

COUNT(*) counts every row in the result set, including rows where all columns are NULL. It answers "how many rows are there?" It is the correct function for counting the total number of records — orders, customers, transactions — regardless of their values.

COUNT(column) counts rows where the specified column is NOT NULL. If delivery_date is NULL for undelivered orders, COUNT(delivery_date) counts only the delivered orders. This answers "how many rows have a value in this column?" It is useful for understanding data completeness — how many records have a phone number, how many orders have been assigned a delivery date.

COUNT(DISTINCT column) counts the number of unique non-NULL values in the column. COUNT(DISTINCT customer_id) from the orders table counts how many distinct customers have placed at least one order — not how many total orders, and not including customers who have never ordered. This answers "how many unique values exist?" It is the cardinality question. A common analytical pattern combining all three: SELECT COUNT(*) AS total_orders, COUNT(delivery_date) AS delivered, COUNT(DISTINCT customer_id) AS unique_customers FROM orders — three different metrics from the same table in one query.

All five aggregate functions (COUNT, SUM, AVG, MIN, MAX) ignore NULL values — they operate only over non-NULL rows. The one exception is COUNT(*), which counts all rows regardless of NULL content. This behaviour is defined by the SQL standard and is consistent across all databases.

The practical consequence that surprises most people: AVG(column) divides by the count of non-NULL values, not by the total row count. If 20 orders exist and 5 have NULL delivery_date, AVG(delivery_date - order_date) divides the sum by 15 — not 20. This gives the average delivery time for delivered orders, which is usually what you want, but it can mislead if you expect the average to account for all orders.

When NULLs should be treated as a specific value rather than ignored, use COALESCE before the aggregate: AVG(COALESCE(delivery_date - order_date, 0)) treats undelivered orders as 0 days and divides by 20 instead of 15. SUM returns NULL (not 0) if all values in the column are NULL — COALESCE(SUM(column), 0) handles this. The rule: always be explicit about whether NULLs should be ignored (use the aggregate directly) or substituted (use COALESCE inside the aggregate) and document the choice in a SQL comment.

Yes — aggregate functions can be used without GROUP BY. When used without GROUP BY, they collapse the entire result set (or the entire filtered result set after WHERE) into a single row. SELECT COUNT(*), SUM(total_amount), AVG(total_amount) FROM orders returns exactly one row containing three numbers that summarise all orders.

Without GROUP BY, all rows that pass the WHERE filter are treated as one group. This is the simplest and most common form of aggregation — getting a single summary metric for a dataset. Total revenue for the month, average order value, the number of active customers, the highest-paid employee salary — all of these are single-row aggregate queries without GROUP BY.

The important rule: when aggregate functions are used without GROUP BY, you cannot include non-aggregated columns in SELECT. SELECT customer_id, COUNT(*) FROM orders without GROUP BY is an error — the database cannot return one row from COUNT(*) and simultaneously return a different customer_id for each row. Every column in SELECT must either be inside an aggregate function or be part of the GROUP BY clause. The only exception is window functions (covered in later modules), which compute aggregates alongside individual rows without collapsing them.

COUNT counts the number of rows (or non-NULL values, or distinct values). SUM adds up the numeric values in a column. They answer fundamentally different questions: COUNT answers "how many?" and SUM answers "how much total?"

Use COUNT when you want a quantity of records: how many orders were placed, how many customers signed up this month, how many products are in stock. Use SUM when you want a total of a numeric value: total revenue, total units sold, total salary budget. A single query often uses both: SELECT COUNT(*) AS order_count, SUM(total_amount) AS total_revenue FROM orders — count tells you the volume, sum tells you the value.

A common confusion: SUM(CASE WHEN condition THEN 1 ELSE 0 END) is equivalent to COUNT(CASE WHEN condition THEN 1 END) for conditional counting. Both count rows where the condition is true. The SUM version with ELSE 0 is slightly more readable for conditional aggregation alongside other aggregates, and it returns 0 (not NULL) when no rows match — COUNT of all NULLs returns 0 anyway, so the difference is subtle. The key insight: SUM of 1s and 0s is the same as COUNT of 1s. This is the foundation of the conditional aggregation pattern.

The delivery rate percentage requires two quantities: the count of delivered orders (the numerator) and the total count of all orders (the denominator). Divide numerator by denominator and multiply by 100. Several equivalent approaches exist in SQL.

Approach 1 — conditional SUM: ROUND(SUM(CASE WHEN order_status = 'Delivered' THEN 1.0 ELSE 0 END) / COUNT(*) * 100, 1) AS delivery_rate_pct. The 1.0 (not integer 1) forces decimal division. This is the most common and most readable approach for conditional percentages.

Approach 2 — AVG of a boolean expression: ROUND(AVG(CASE WHEN order_status = 'Delivered' THEN 1.0 ELSE 0 END) * 100, 1). AVG of 0s and 1s gives the proportion directly (between 0 and 1), then multiply by 100 for the percentage. This is mathematically identical to approach 1. Both produce the same result. The choice is style preference — approach 1 (SUM / COUNT) is more explicit about what the numerator and denominator are, which aids readability. Add a NULLIF to prevent division by zero when the table might be empty: SUM(...) / NULLIF(COUNT(*), 0) * 100.

Cause: You included a non-aggregated column in SELECT alongside aggregate functions, without listing that column in GROUP BY. When aggregate functions are used, every column in SELECT must either be inside an aggregate function (COUNT, SUM, AVG, MIN, MAX) or be listed in GROUP BY. The database cannot return one summary row from COUNT(*) and simultaneously return a different customer_id per row.

Fix: Either add the column to GROUP BY: SELECT customer_id, COUNT(*) FROM orders GROUP BY customer_id. Or wrap the column in an aggregate function: SELECT MAX(customer_id), COUNT(*) FROM orders. Or remove the column from SELECT if it is not needed. The rule is: aggregate queries either return one summary row (no GROUP BY) or one row per group (with GROUP BY listing all non-aggregated columns).

Cause: SUM of zero rows returns NULL, not 0. When no rows match the WHERE condition, SUM has no values to add — and the result is NULL rather than the more intuitive 0. This happens when filtering by a date range with no data, a status with no matching rows, or a store with no orders.

Fix: Wrap SUM in COALESCE to replace NULL with 0: COALESCE(SUM(total_amount), 0) AS total_revenue. This handles the empty-set case cleanly. The same applies to AVG and other aggregates: COALESCE(AVG(total_amount), 0) AS avg_revenue. Note that COUNT(*) already returns 0 for empty sets (not NULL) — only SUM, AVG, MIN, and MAX return NULL when no rows match.

Cause: AVG ignores NULL values. If the column being averaged has NULLs (for example, delivery_date - order_date is NULL for undelivered orders), AVG only divides by the count of non-NULL rows. With 20 orders and 5 undelivered (NULL delivery_date), AVG divides by 15, not 20. The result is the average for the delivered subset only — which is higher than the average including all orders.

Fix: If you want NULLs included as a specific value: AVG(COALESCE(delivery_date - order_date, 0)) treats undelivered orders as 0 days. If you want the average of delivered orders only (NULL exclusion is correct): document this explicitly in a comment and verify the denominator: SELECT COUNT(*) AS total, COUNT(delivery_date) AS non_null_count to confirm how many rows AVG is dividing by. The key is to be intentional about which question you are answering.

Cause: The column has more unique values than expected — possibly due to case sensitivity, whitespace, or formatting inconsistencies. 'Seattle' and 'bangalore' and 'Seattle ' (trailing space) are three distinct values in PostgreSQL's case-sensitive comparison. COUNT(DISTINCT city) returns 3 for what you might consider the same city.

Fix: Normalise the values before counting: COUNT(DISTINCT LOWER(TRIM(city))) counts distinct cities after converting to lowercase and removing whitespace — treating 'Seattle', 'bangalore', and 'Seattle ' as the same value. To investigate inconsistencies: SELECT DISTINCT city FROM customers ORDER BY city — examine the result for near-duplicates. Fix at the data level (UPDATE to canonical forms) for a permanent solution.

Cause: The CASE WHEN expression inside SUM has a logic error — conditions are in the wrong order, a condition is missing, or the ELSE clause is absent (returning NULL instead of 0 for non-matching rows). SUM(CASE WHEN condition THEN amount END) sums NULL for non-matching rows, which is ignored by SUM — equivalent to ELSE 0. But SUM(CASE WHEN condition THEN amount ELSE amount END) incorrectly counts everything.

Fix: Always include an explicit ELSE 0 in SUM(CASE WHEN): SUM(CASE WHEN order_status = 'Delivered' THEN total_amount ELSE 0 END). This makes the intent clear and prevents NULL accumulation surprises. To debug, run the CASE WHEN as a SELECT column first — no GROUP BY, just the CASE expression per row — and verify that each row produces the expected value before wrapping in SUM.

Write a single query that produces the FreshCart monthly executive summary. Show: total orders, unique customers, total delivered revenue (rounded to 2 decimal places), average order value for delivered orders (rounded to 2 decimal places), the count of orders by status (delivered, cancelled, returned, processing as separate columns), percentage of orders delivered (rounded to 1 decimal place), and the most expensive single order. All from the orders table — no GROUP BY needed, one summary row.