SQL Aggregate Functions for Analysts

Reporting is mostly the act of turning thousands of individual rows into a handful of numbers a manager can read at a glance. "What was total revenue?" is one number from many orders. "What is our average order value?" is another. The functions that perform this collapse — COUNT, SUM, AVG, MIN and MAX — are called aggregate functions, and they are the workhorses of analytics SQL.

This tutorial covers the five aggregates on their own, against the whole table. Splitting them per region or per month comes next with GROUP BY and HAVING, but you must understand the aggregates first. It sits mid-way through the SQL for analytics path.

The five aggregate functions

Each aggregate takes a column of many values and returns one value. Here is the sample orders table:

-- orders
order_id | customer | region | amount | order_date
---------+----------+--------+--------+-----------
1001     | Aarti    | South  | 1200   | 2026-01-05
1002     | Bhaskar  | South  | 8500   | 2026-01-11
1003     | Chitra   | West   | 1200   | 2026-02-02
1004     | Aarti    | South  | 600    | 2026-02-18
1005     | Devan    | North  | 8500   | 2026-03-09
1006     | Chitra   | West   | 2100   | 2026-03-22

Run all five over the whole table:

SELECT COUNT(*)    AS total_orders,
       SUM(amount) AS total_revenue,
       AVG(amount) AS avg_order_value,
       MIN(amount) AS smallest_order,
       MAX(amount) AS largest_order
FROM orders;

total_orders | total_revenue | avg_order_value | smallest_order | largest_order
-------------+---------------+-----------------+----------------+--------------
6            | 22100         | 3683.33         | 600            | 8500

With no GROUP BY, the aggregate treats the entire table as one group and returns a single summary row. Six orders, total revenue of 22100, an average order value near 3683, and a range from 600 to 8500. That one query is a complete top-line business summary.

COUNT and its variations

COUNT is the most nuanced of the five. There are three forms and they answer different questions:

SELECT COUNT(*)                 AS all_rows,
       COUNT(region)            AS rows_with_region,
       COUNT(DISTINCT customer) AS unique_customers
FROM orders;

all_rows | rows_with_region | unique_customers
---------+------------------+-----------------
6        | 6                | 4

COUNT(*) counts every row. COUNT(region) counts only rows where region is not NULL — so if some orders had a missing region, this number would be lower than COUNT(*). COUNT(DISTINCT customer) counts unique customers: there are six orders but only four distinct customers, because Aarti and Chitra each ordered twice. Choosing the wrong form is one of the most common reporting bugs — "how many customers" almost always means COUNT(DISTINCT customer), not COUNT(*).

Rounding and readability

AVG often returns long decimals. ROUND makes reports readable:

SELECT ROUND(AVG(amount), 2) AS avg_order_value
FROM orders;

avg_order_value
---------------
3683.33

For currency, analysts usually round to whole units or two decimals depending on the audience. Small formatting choices like this are what separate a raw query dump from a report people trust.

How NULLs change the answer

Aggregate functions treat NULL as "not there" rather than as zero, and that distinction changes numbers. If two of the six orders had a NULL amount:

• COUNT(*) is still 6.
• COUNT(amount) is 4.
• SUM(amount) totals only the four known amounts.
• AVG(amount) divides that sum by 4, not 6.

So AVG(amount) and SUM(amount) / COUNT(*) can disagree. Which is correct depends on whether a missing amount should count as zero. If it should, use AVG(COALESCE(amount, 0)) to treat NULLs as zero. Deciding this consciously — rather than accepting whatever the database returns — is the analyst's responsibility.

Practical usage: KPI tiles and top-line numbers

The single-row summary above is exactly what feeds the "KPI tiles" at the top of a dashboard: total revenue, order count, average order value, biggest deal. Analysts write dozens of these. Often you add a WHERE to scope the KPI to a period — "total revenue this quarter" is the same query with WHERE order_date BETWEEN .... Combining a filter with an aggregate is the most common two-clause query in the job.

SELECT SUM(amount) AS q1_revenue,
       COUNT(*)    AS q1_orders
FROM orders
WHERE order_date BETWEEN '2026-01-01' AND '2026-03-31';

Common mistakes

• Using COUNT(*) when the question means distinct. "How many customers" answered with COUNT(*) overcounts anyone who ordered more than once. Use COUNT(DISTINCT customer).
• Assuming AVG treats NULL as zero. It does not; it skips NULLs. When missing values should pull the average down, wrap the column in COALESCE.
• Putting an aggregate in WHERE. WHERE SUM(amount) > 1000 fails, because WHERE runs before aggregation. Filter aggregates with HAVING after a GROUP BY.
• Summing a non-additive column. Summing a percentage, a ratio or an average column produces a meaningless number. Only sum genuinely additive measures like amount or quantity.

In interviews

Expect quick aggregate questions — "total revenue", "average order value", "number of unique customers" — as the on-ramp to harder ones. The revealing follow-ups target edge cases: "how does a NULL amount affect your average?", "what is the difference between COUNT(*) and COUNT(customer)?", "why can't you filter on SUM in WHERE?" Answering these crisply shows you understand what the database is actually doing, not just the syntax.

Where this fits in your learning path

Aggregate functions are the summarizing engine of analytics. On their own they give top-line numbers; the moment you want those numbers broken down per region, per month or per product, you combine them with GROUP BY and HAVING. To rank the results, pair them with ORDER BY and LIMIT. Together these form the reporting core of the data analyst roadmap.