Reporting Aggregated Data in SQL: Aggregates, GROUP BY, and HAVING

An aggregate function takes many rows and returns one value. It summarises the data rather than listing it. Five functions cover almost every case — COUNT, SUM, AVG, MIN, MAX.

SELECT COUNT(*)         AS orders,
       SUM(order_amount) AS total_revenue,
       AVG(order_amount) AS avg_order_value
FROM   orders;

One row comes back with three numbers. Seven orders, 76,200.456 in total revenue, and 10,885.78 as the average order value. Every other column in the table — order id, customer id, status, date — has been collapsed away by the aggregation.

Most aggregate queries pair the function with GROUP BY, which produces one summary per category instead of one summary for the whole table. Without GROUP BY you get a global summary. With it you get a breakdown.

COUNT comes in two flavours, and the difference matters more than beginners expect.

SELECT COUNT(*)         AS all_customers,
       COUNT(credit_limit) AS with_limit
FROM   customers;

COUNT(*) returns 7 — every row in the table. COUNT(credit_limit) returns 6 because Fiona Mart has a NULL credit limit and the column version skips NULLs. The wildcard counts rows. The named column counts values.

Real-world use cases are simple. How many orders came in today? How many distinct customers placed an order this month? How many employees are still on the payroll? Each of these is a COUNT with a WHERE clause to narrow the rows first.

SUM adds up every numeric value in a column. NULLs are skipped automatically — they do not contribute to the total, and they do not cause an error.

SELECT SUM(order_amount) AS total_revenue
FROM   orders;

That returns 76,200.456 — the sum of all seven order amounts. Add a filter and the sum only includes the rows that survive.

SELECT SUM(order_amount) AS delivered_revenue
FROM   orders
WHERE  order_status = 'Delivered';

Now only the four Delivered orders count. Paired with GROUP BY, SUM gives you per-group totals:

SELECT customer_id, SUM(order_amount) AS total_spend
FROM   orders
GROUP BY customer_id;

One row per customer with their own total. This is the pattern behind almost every revenue report you will ever write. The only thing to watch for is that SUM only works on numbers — feed it a text column and you get an error.

AVG returns the mean of a numeric column. Like the other aggregates, it skips NULLs — the rows that do not have a value do not get counted in either the numerator or the denominator.

SELECT AVG(order_amount) AS avg_order
FROM   orders;

That returns 10,885.78 — 76,200.456 divided by 7. Add a filter and the average shrinks to just the matching rows.

SELECT AVG(credit_limit) AS avg_credit
FROM   customers;

That returns 54,166.67 — 325,000 divided by 6, not by 7. Fiona Mart's NULL does not contribute to the sum and does not count in the divisor either. If you want the average across all seven customers, including the one with no limit, wrap the column in COALESCE first: AVG(COALESCE(credit_limit, 0)). That divides by 7 and gives 46,428.57.

This NULL behaviour is the single most common source of confusion about AVG. It is worth knowing cold — interviewers ask about it, and in production it is exactly the kind of thing that produces "why does the report say the average went up when I added a row?" bugs.

MAX returns the highest value in a column. It works on numbers, dates, and text — anything with a defined ordering.

SELECT MAX(order_amount) AS largest_order,
       MAX(order_date)   AS most_recent
FROM   orders;

Largest order comes back as 22,000.5 — order 1004. Most recent date comes back as 2024-03-22 — order 1006. On text columns, MAX returns the alphabetically last value.

The most common use is "find the top item". What is the highest salary? What is the latest signup? What is the biggest discount anyone has ever been given? Each one is a MAX with a filter. Paired with GROUP BY, it answers the per-category version of the same question:

SELECT customer_id, MAX(order_amount) AS biggest_order
FROM   orders
GROUP BY customer_id;

One row per customer with their largest single order. That is a common first step in any ranking report.

MIN is the mirror of MAX. It returns the smallest value in a column — smallest number, earliest date, alphabetically first text.

SELECT MIN(order_amount) AS smallest_order,
       MIN(order_date)   AS earliest_order
FROM   orders;

Smallest order is 3,200.999 (order 1005). Earliest date is 2024-01-15 (order 1001). Both ignore NULLs, so the two orders without dates do not affect the minimum.

The typical uses mirror MAX. What is the earliest hire date? What is the cheapest product? What is the lowest grade that still passes? All of them are MIN queries. And with GROUP BY, you get the per-group version:

SELECT customer_id, MIN(order_amount) AS smallest_order
FROM   orders
GROUP BY customer_id;

One row per customer with their lowest single order. Combined with MAX, this gives you the range each customer's orders span.

GROUP BY splits the rows into groups based on the values in the columns you list, then runs the aggregate functions on each group separately.

SELECT customer_id,
       COUNT(*)          AS order_count,
       SUM(order_amount) AS total_spend
FROM   orders
GROUP BY customer_id;

Four rows come back — one per customer who has at least one order. Each row shows that customer's order count and total spend.

The rule that catches people out is that every column in the SELECT list must either appear in GROUP BY or be wrapped in an aggregate function. Try to select a column that is neither, and the database refuses to run the query. That is not the database being fussy — it genuinely does not know which value to return for a group with many rows.

One scenario worth knowing: GROUP BY only produces rows for groups that exist in the source data. Customers with no orders do not appear. To include them, use a LEFT JOIN from customers to orders — that forces every customer to have at least one row to group, even if that row's order columns are NULL.

HAVING filters groups after GROUP BY has produced them. It is the counterpart to WHERE, which filters individual rows before grouping.

SELECT customer_id,
       SUM(order_amount) AS total_spend
FROM   orders
GROUP BY customer_id
HAVING SUM(order_amount) > 10000;

Only the customers whose total spend exceeds 10,000 come back. On our data that is C001 and C004. Everyone else has their row dropped after the sum has been computed.

Try to write WHERE SUM(order_amount) > 10000 instead and the database stops you with a syntax error about aggregates not being allowed in WHERE. That error is worth remembering because it is the fastest way to remind yourself where HAVING belongs.

HAVING works with any aggregate. HAVING COUNT(*) > 2 finds groups with more than two rows. HAVING AVG(credit_limit) > 50000 finds groups whose average exceeds a threshold. HAVING MAX(order_amount) > 15000 finds groups that contain at least one big order.

Combine GROUP BY with SUM and you get one total per category. That is the pattern behind almost every revenue, expense, or volume report ever written.

SELECT customer_id,
       SUM(order_amount) AS total_spend
FROM   orders
GROUP BY customer_id
ORDER BY total_spend DESC;

The ORDER BY at the end makes the report readable — biggest spender at the top. That sort happens after the totals are computed, so you can reference the aggregate directly.

Multiple aggregates in the same query work fine, and they often make a report more useful:

SELECT customer_id,
       COUNT(*)               AS order_count,
       SUM(order_amount)      AS total_spend,
       AVG(order_amount)      AS avg_order,
       MIN(order_amount)      AS smallest_order,
       MAX(order_amount)      AS largest_order
FROM   orders
GROUP BY customer_id;

Six columns, one row per customer. That gives a reader the complete picture of each customer's order behaviour without any further digging. This is the shape of a customer summary report.

COUNT(DISTINCT column_name) counts unique non-NULL values. It removes duplicates before counting.

SELECT COUNT(region)         AS non_null_regions,
       COUNT(DISTINCT region) AS unique_regions
FROM   customers;

The first returns 7 — every customer's region value, including duplicates. The second returns 3 — North, South, and West, each counted once.

This is the pattern for "how many distinct X do we have?" — distinct customers who placed an order, distinct products in a category, distinct countries on file. And with GROUP BY, you get the per-group version:

SELECT customer_id,
       COUNT(DISTINCT order_status) AS distinct_statuses
FROM   orders
GROUP BY customer_id;

That returns the number of different statuses each customer's orders have been in. C001 has one (Delivered). C002 has two (Delivered and Shipped). This kind of query shows up in behavioural analytics where you want to know how varied a customer's activity is.

One caution: COUNT(DISTINCT) can be slower than plain COUNT because the database has to sort or hash the values to find the unique ones. On large tables, that difference can be significant.

Join first, aggregate second. The join produces the working set of rows, and the aggregate summarises them.

SELECT c.customer_name,
       SUM(o.order_amount) AS total_spend
FROM   customers c
JOIN   orders o ON c.customer_id = o.customer_id
GROUP BY c.customer_name
ORDER BY total_spend DESC;

Each customer's name gets paired with their total spend. Alice Corp comes back with 28,500.58, Bob Traders with 7,701.79. The join is what lets you show the customer's name instead of just their id, and the aggregate then does its job on the combined row set.

The subtlety worth knowing: with an INNER JOIN, customers who have no orders drop out entirely. Only customers who have at least one matching order appear. If you want to include customers with no orders, switch to LEFT JOIN and wrap the SUM in COALESCE so the totals show 0 instead of NULL.

You can also join on more than two tables and aggregate across all of them. Aggregate functions work the same whether the source is one table or a joined set of ten.

List more than one column in the GROUP BY clause and you get one row per combination of values across those columns.

SELECT customer_id,
       order_status,
       COUNT(*)          AS status_count,
       SUM(order_amount) AS status_total
FROM   orders
GROUP BY customer_id, order_status;

Now the groups are (customer, status) pairs. C001 shows up once, with a single Delivered row containing three orders totalling 28,500.58. C002 shows up twice — once for Delivered, once for Shipped. This is the pattern behind any breakdown report where the reader wants to see sub-totals inside each top-level category.

The order of columns in GROUP BY does not change the result — the grouping is the same regardless. But the order in the SELECT list affects how the output reads, so put the primary grouping column first.

Watch out for the total number of groups. Two columns with ten values each can produce up to a hundred groups, and three columns can explode that further. If a grouped query is unexpectedly slow, the number of distinct combinations is often the reason.

Rolling aggregates compute a value over a moving window of rows rather than over a fixed group. You use window functions rather than GROUP BY for this, because the output keeps every row and adds a computed column beside it.

SELECT order_id,
       order_date,
       order_amount,
       SUM(order_amount) OVER (ORDER BY order_date) AS running_total
FROM   orders
WHERE  order_date IS NOT NULL
ORDER BY order_date;

The OVER (ORDER BY order_date) clause tells the database to sum the amounts from the first row up to the current row. Each row gets a cumulative total as of its own date. The output has every original row plus one new column.

For a moving average over a fixed number of preceding rows, you specify a window frame:

SELECT order_id,
       AVG(order_amount) OVER (
           ORDER BY order_date
           ROWS BETWEEN 2 PRECEDING AND CURRENT ROW
       ) AS moving_avg_3
FROM   orders
WHERE  order_date IS NOT NULL;

Now each row carries the average of itself plus the two rows before it. This is the standard pattern for smoothing out short-term noise in a time series.

Rolling aggregates are a window-function topic, not a plain aggregate topic, but interviewers often bridge the two because the function names are identical. Knowing that SUM works both ways — grouped or windowed — is a good signal that you understand the difference.

You extract the date part you want with a function, then group by the result. The exact function depends on the database.

SELECT EXTRACT(YEAR  FROM order_date) AS order_year,
       EXTRACT(MONTH FROM order_date) AS order_month,
       COUNT(*)          AS orders_in_month,
       SUM(order_amount) AS monthly_revenue
FROM   orders
WHERE  order_date IS NOT NULL
GROUP BY EXTRACT(YEAR FROM order_date),
         EXTRACT(MONTH FROM order_date)
ORDER BY order_year, order_month;

One row per month that has at least one order. On our data that is January, February, and March of 2024 — three rows. The WHERE clause drops the two orders with NULL dates so they do not create an unlabeled group.

Oracle and PostgreSQL use EXTRACT. SQL Server uses YEAR(), MONTH(), DATEPART. MySQL has YEAR() and MONTH() too. The syntax varies, the idea does not.

An alternative for monthly grouping is TRUNC(order_date, 'MM'), which returns the first of the month. Grouping by that keeps the result as a date instead of two separate numbers, which is often more convenient when the output feeds a chart. Both work; pick based on what downstream code expects.

Divide each group's total by the grand total, then multiply by 100. The grand total usually comes from a subquery or a window function.

SELECT customer_id,
       SUM(order_amount)                                 AS customer_total,
       SUM(order_amount) * 100.0 /
         (SELECT SUM(order_amount) FROM orders)          AS pct_of_total
FROM   orders
GROUP BY customer_id
ORDER BY customer_total DESC;

Each row shows the customer's total and what percentage of overall revenue that represents. On our data, C001's 28,500.58 is roughly 37% of the 76,200 total.

The subquery runs once and produces a single number. Every group divides by that same number. If you tried to use SUM(order_amount) / SUM(order_amount) inside the SELECT, both would refer to the group and the result would always be 1 — that is the mistake to avoid.

Window functions give a cleaner version of the same idea. SUM(SUM(order_amount)) OVER () computes the grand total without a separate subquery, and the query reads more naturally. Either approach works; the subquery version is more portable across older database versions.

DISTINCT removes duplicate rows from the result. It is not an aggregate function, and it does not summarise anything — it filters.

SELECT DISTINCT region
FROM   customers;

That returns three rows — North, South, West — one per unique region. If there were twenty customers, the output would still be three rows.

The subtlety is that DISTINCT applies to the whole row you select, not to a single column. SELECT DISTINCT region, credit_limit returns unique pairs of region and credit limit, which is not the same as unique regions.

Where DISTINCT overlaps with aggregate logic is inside COUNT. COUNT(DISTINCT region) counts unique regions while also producing a single number, which is what you would normally want from an aggregate. Outside of that specific usage, if you need summaries, use GROUP BY. If you need to see the distinct values themselves, use DISTINCT. The two solve different problems.

COUNT(DISTINCT column_name) counts how many different values appear in a column. Duplicates are ignored, NULLs are ignored.

SELECT COUNT(DISTINCT region)         AS unique_regions,
       COUNT(DISTINCT customer_id)    AS customers_with_orders
FROM   orders o
JOIN   customers c ON o.customer_id = c.customer_id;

Two different counts in the same query. The first returns 3 — the number of distinct regions. The second returns 4 — the number of distinct customers who placed orders in our sample.

This is the standard "how many distinct X" pattern. How many unique products were sold this month? How many different payment methods were used? How many countries do our customers live in?

Combined with GROUP BY, it gives per-group counts of distinct values:

SELECT customer_id,
       COUNT(DISTINCT order_status) AS distinct_statuses
FROM   orders
GROUP BY customer_id;

Each customer row shows how many different statuses their orders have been in. It is a small but genuinely useful metric when you are profiling customer behaviour.

Every aggregate function except COUNT(*) ignores NULLs by default. That means NULLs silently drop out of sums, averages, and min/max calculations unless you handle them explicitly.

SELECT AVG(credit_limit)                 AS avg_excluding_nulls,
       AVG(COALESCE(credit_limit, 0))   AS avg_including_nulls
FROM   customers;

The first returns 54,166.67 — 325,000 divided by 6. The second returns 46,428.57 — the same total divided by 7. Fiona Mart's NULL counts in the second version because COALESCE replaces it with 0 before the average is computed.

Which one is correct depends entirely on what the report means. "Average credit limit among customers who have one" is the first. "Average across all customers, treating missing as zero" is the second. Neither is universally right, and interviewers want to hear that you know the difference.

To count NULLs explicitly, wrap them in a CASE expression or use the subtraction trick:

SELECT COUNT(*) - COUNT(credit_limit) AS null_count
FROM   customers;

That returns 1 — Fiona Mart. Compact and reliable.

Wrap the aggregate function's argument in a CASE expression and only the rows that match the condition contribute to the total. The rows that fail the condition contribute zero (or NULL, depending on the else branch).

SELECT customer_id,
       SUM(CASE WHEN order_status = 'Delivered' THEN order_amount ELSE 0 END) AS delivered_total,
       SUM(CASE WHEN order_status = 'Shipped'   THEN order_amount ELSE 0 END) AS shipped_total
FROM   orders
GROUP BY customer_id;

Two conditionals in one query. The first column totals each customer's delivered revenue. The second totals their shipped revenue. Every customer row shows both numbers side by side.

This is the pattern for what is called "pivoting" or "conditional aggregation" — breaking one measure into multiple columns based on a category. Without CASE inside the aggregate, you would have to run separate queries and join them, or use a database-specific pivot feature.

The conditional aggregation pattern is worth knowing because it comes up constantly in reporting. Sales by channel. Orders by status. Signups by acquisition source. All of them fit the same shape: one GROUP BY on the entity, one CASE per category you want as a column.

COUNT(column_name) and SUM(column_name) both skip NULLs. That is usually what you want, but not always.

SELECT COUNT(*)                              AS all_rows,
       COUNT(credit_limit)                   AS with_value,
       SUM(credit_limit)                     AS total,
       SUM(COALESCE(credit_limit, 0))        AS total_with_zeros
FROM   customers;

The first two columns count rows and non-NULL values — 7 and 6. SUM(credit_limit) and SUM(COALESCE(credit_limit, 0)) both return the same number here (325,000) because adding a 0 to a total changes nothing. The distinction matters more when you want to count NULLs or average with NULLs included.

To count NULLs themselves:

SELECT COUNT(CASE WHEN credit_limit IS NULL THEN 1 END) AS null_count
FROM   customers;

That returns 1 — Fiona Mart. The CASE produces a 1 for NULL rows and NULL for everything else. Since COUNT skips NULLs, only the ones with a real value get counted.

The general rule: be deliberate about NULL handling. Do not assume the default behaviour matches what the report needs. If a NULL is meaningful — say, it means "not yet assessed" rather than "zero" — then preserving the distinction is important. If it is just missing data, replacing it with a real value is usually cleaner.

You can group by a CASE expression directly. Instead of grouping by a raw column, you group by a computed category, and the result shows one row per category.

SELECT CASE
           WHEN order_amount > 10000 THEN 'Large'
           WHEN order_amount > 5000  THEN 'Medium'
           ELSE 'Small'
       END            AS size_category,
       COUNT(*)       AS order_count,
       SUM(order_amount) AS total
FROM   orders
GROUP BY CASE
           WHEN order_amount > 10000 THEN 'Large'
           WHEN order_amount > 5000  THEN 'Medium'
           ELSE 'Small'
       END;

Three rows back — one for each category. Large orders, medium orders, small orders. Each has its own count and total. The output reads more naturally than grouping by raw amounts, because the reader immediately understands what "Large" means.

Some databases let you reference the alias in the GROUP BY instead of repeating the whole expression:

GROUP BY size_category

That works in MySQL and PostgreSQL. Oracle and SQL Server require the full expression in GROUP BY. Check your database's behaviour before assuming either form works.

You extract or truncate the date to the interval you want, then group by the result.

SELECT EXTRACT(YEAR  FROM order_date) AS order_year,
       EXTRACT(MONTH FROM order_date) AS order_month,
       SUM(order_amount) AS monthly_revenue
FROM   orders
WHERE  order_date IS NOT NULL
GROUP BY EXTRACT(YEAR FROM order_date),
         EXTRACT(MONTH FROM order_date)
ORDER BY order_year, order_month;

One row per month. That is the pattern for monthly revenue, signups, or any other metric you want to track over time.

For weekly grouping, use TRUNC(order_date, 'IW') in Oracle or DATE_TRUNC('week', order_date) in PostgreSQL. Both return the Monday of the week, so every date in the same week lands on the same value.

-- PostgreSQL
SELECT DATE_TRUNC('week', order_date) AS week_start,
       COUNT(*)                       AS orders_in_week
FROM   orders
WHERE  order_date IS NOT NULL
GROUP BY DATE_TRUNC('week', order_date)
ORDER BY week_start;

Daily grouping works the same way — TRUNC(order_date) in Oracle, DATE(order_date) in MySQL, CAST(order_date AS DATE) in SQL Server. Once the dates are truncated to a common value, the grouping is straightforward.

The one pitfall with time grouping is timezone. If your dates are stored as UTC and your users think in local time, a month boundary can shift by a few hours. For reports that go to people in specific timezones, convert the timestamp to the target timezone before extracting parts.

Most databases do not have a MEDIAN function. Oracle does, and so do some others, but it is not universal. When you need a portable way to compute it, you use a window function to rank the rows and then pick the middle one.

WITH ordered AS (
    SELECT order_amount,
           ROW_NUMBER() OVER (ORDER BY order_amount) AS rn,
           COUNT(*)     OVER ()                     AS total
    FROM   orders
)
SELECT AVG(order_amount) AS median_order_amount
FROM   ordered
WHERE  rn IN (FLOOR((total + 1) / 2), CEIL((total + 1) / 2));

The CTE ranks every row by its order amount and records the total row count. The outer query then keeps only the middle one (if there is an odd number of rows) or the middle two (if even). The AVG at the end handles both cases — for odd counts it averages a single value, and for even counts it averages the two middle values.

On our data, seven orders means the fourth row by amount is the median. Working from the sorted list — 3,200.999, 4,500.789, 7,500.123, 9,000, 12,000.456, 18,000.25, 22,000.5 — the fourth value is 9,000. That is the median.

Median is a useful statistic because it resists outliers. If one order in a million was worth 100 million, the average would be dragged way up but the median would stay put. Whenever you are looking at a distribution that might have extreme values — income, transaction sizes, session durations — the median is often more informative than the mean.

Hierarchical data has parent-child relationships — a manager and their direct reports, a category and its subcategories, a folder and its files. Aggregation over a hierarchy usually means rolling totals up from leaf rows to their parents.

For a single level of rollup, a self-join does the trick. Give the customers table a referred_by_id column and you can sum the referred customers' spend per referrer:

SELECT c1.customer_name AS referrer,
       SUM(o.order_amount) AS referred_spend
FROM   customers c1
JOIN   customers c2 ON c2.referred_by_id = c1.customer_id
JOIN   orders    o  ON o.customer_id = c2.customer_id
GROUP BY c1.customer_name;

That gives you per-referrer totals in a single query. It is the standard pattern for one-level hierarchies.

For deeper hierarchies — a manager with sub-managers who themselves have reports — you need a recursive query. PostgreSQL, MySQL 8+, and SQL Server all support WITH RECURSIVE. The structure is a bit involved, but the idea is that you seed the query with the top of the tree, then repeatedly join the result back to the source until you reach the leaves. Once you have the flattened hierarchy, aggregating is just a GROUP BY away.

The interview angle is usually simpler than the practical one. Interviewers ask this to see if you know that a self-join handles one level and that recursion handles more. Mentioning both is enough to show you understand the shape of the problem.

Groups with no matching rows do not appear in the output by default. If you group orders by customer and one customer has never placed an order, that customer does not show up at all — there is nothing to group.

This matters in reports where the reader expects to see every category, even the ones with zero activity. Departments with no employees. Products with no sales. Customers with no orders. A missing row is easy to misread as "no data available" when the truth is "this category exists and happens to have zero activity".

The fix is a LEFT JOIN from the dimension table to the fact table, plus a COALESCE to turn the resulting NULLs into zeros:

SELECT c.customer_name,
       COUNT(o.order_id)                       AS order_count,
       COALESCE(SUM(o.order_amount), 0)        AS total_spend
FROM   customers c
LEFT JOIN orders o ON c.customer_id = o.customer_id
GROUP BY c.customer_name;

Now every customer appears. Ethan Supplies, who has no orders, comes back with a count of 0 and a spend of 0. The reader sees the full list of customers, not just the active ones.

Two things to watch for in this pattern. First, the COALESCE is not always necessary — SUM of an empty set is NULL, but COUNT(order_id) already returns 0. Second, if the group has other columns you would normally filter on, remember that NULL comparison rules apply after the LEFT JOIN.