Combining Aggregates and Grouping

Real applications rarely need only one summary value. A business report may need to show the number of orders, total revenue, average order value, smallest order, and largest order for each category.

SQL can calculate all these values in one grouped query.

For example, an online store may want to answer:
- How many orders does each category have?
- What is the total sales amount for each category?
- What is the average order amount?
- What are the minimum and maximum order amounts?
- Which categories contribute the largest percentage of sales?

The main tools are:
- GROUP BY to define each group.
- COUNT to count rows or non-NULL values.
- SUM to calculate totals.
- AVG to calculate averages.
- MIN and MAX to find the smallest and largest non-NULL values.
- HAVING to filter groups.
- ORDER BY to sort the report.

This lesson uses PostgreSQL syntax and illustrative tables. The sample output values are examples, not results from an executed database.

Consider the orders table:

orders(order_id, customer_id, category, amount, status)

The aggregate functions answer different questions.

COUNT(*)
Counts all rows in a group.

COUNT(amount)
Counts rows where amount is not NULL.

SUM(amount)
Adds the non-NULL amount values.

AVG(amount)
Calculates the average of non-NULL amount values.

MIN(amount)
Returns the smallest non-NULL amount.

MAX(amount)
Returns the largest non-NULL amount.

Important:
- COUNT(*) can return zero for an empty input in an aggregate query without GROUP BY.
- SUM and AVG return NULL when there are no non-NULL input values.
- MIN and MAX also return NULL when there are no non-NULL input values.
- A grouped query over an empty input produces no groups.

Choose each function according to the question the report needs to answer.

To calculate multiple summaries for every category, place each aggregate in the SELECT list.

General syntax:

SELECT
grouping_column,
aggregate_function_1(column_name),
aggregate_function_2(column_name)
FROM table_name
GROUP BY grouping_column;

Example:

SELECT
category,
COUNT(*) AS order_count,
SUM(amount) AS total_sales,
AVG(amount) AS average_order_amount,
MIN(amount) AS smallest_order,
MAX(amount) AS largest_order
FROM orders
GROUP BY category;

Each output row represents one category. All aggregate values on that row summarize the same category's input rows.

Use meaningful aliases so that the output is understandable to people and backend services.

The columns in GROUP BY define what one output row means.

Example A — one row per category:

GROUP BY category

Example B — one row per customer:

GROUP BY customer_id

Example C — one row per category and status combination:

GROUP BY category, status

For example, grouping by category and status can produce separate rows for:
- Electronics, Completed
- Electronics, Pending
- Books, Completed
- Books, Pending

A common mistake is adding an extra grouping column without realizing that it changes the report's meaning.

If a report needs one row per category, grouping by both category and status creates a more detailed report instead.

A multi-dimensional report summarizes data across more than one business dimension.

Suppose a company wants sales totals by department and product category.

Assume the table:

sales(sale_id, department, category, quantity, unit_price)

The total for each sale row can be calculated as quantity * unit_price.

The report can group by department and category, then calculate:
- Number of sales records.
- Total quantity sold.
- Total revenue.
- Average revenue per sale record.
- Minimum and maximum sale-row revenue.

Be precise about what the count means. COUNT(*) counts sales records, not necessarily individual units or unique customers.

Also, if the table stores one row per sale line, a COUNT(*) result is a count of sale lines rather than a count of distinct orders.

A production report often needs to filter input records, calculate several aggregates, and then filter the resulting groups.

Requirement:
For completed orders, show categories with at least three orders and total sales of at least 1,000.

The query works in three main stages:

1. WHERE keeps only completed orders.
2. GROUP BY forms one group per category and calculates the aggregates.
3. HAVING keeps groups that satisfy both aggregate conditions.

The total sales and order count refer only to the rows that passed WHERE.

Remember that changing WHERE can change every aggregate value in the report.

ORDER BY can sort the grouped results using aggregate expressions or their output aliases.

Example:

ORDER BY total_sales DESC

This sorts the groups from highest to lowest total sales.

You can also sort using:

ORDER BY order_count DESC, category ASC

This puts groups with more orders first. When two groups have the same count, category is used as a secondary sort key.

Use NULLS FIRST or NULLS LAST when the placement of NULL sort values matters.

Important:
GROUP BY does not guarantee a particular output order. Use ORDER BY whenever ordering is part of the requirement.

Percentage contribution answers questions such as:
- What percentage of total revenue comes from each category?
- What percentage of all orders belongs to each department?
- What percentage of company sales comes from one product group?

The general formula is:

percentage = (group_value / overall_total) * 100

The numerator is the value for one group.
The denominator is the overall total across the relevant population.

For a category's revenue contribution, the numerator is the category's SUM(amount). The denominator must be the total amount across all included categories.

A clear approach is:
1. Calculate the category totals.
2. Calculate the overall total.
3. Divide each category total by that overall total.
4. Multiply by 100.

Use NULLIF to protect the division if the denominator is zero.

Be careful with filtering: if you filter out categories before calculating the denominator, the percentages describe the filtered population, not necessarily all company sales.

Consider a percentage calculation:

100 * group_total / overall_total

If the operands are integers, integer division can discard the fractional part before the result is converted to another type. In PostgreSQL, using 100.0 helps make the calculation use a numeric value with fractional precision.

For example, 1 divided by 3 using integer arithmetic does not preserve the repeating decimal.

Use:

100.0 * group_total / NULLIF(overall_total, 0)

NULLIF(overall_total, 0) returns NULL when the denominator is zero. That prevents a division-by-zero error, but the resulting percentage is NULL because the percentage is undefined.

This is preferable to inventing a percentage of zero when there is no meaningful denominator.

If a report needs a specific display value for an undefined percentage, handle that explicitly in the reporting layer or with a carefully chosen COALESCE expression.

A ratio compares two related quantities.

Examples:
- Completed orders divided by all orders.
- Units sold divided by the number of sale lines.
- Total revenue divided by order count.

For a ratio of total revenue to order count, first decide whether order_count means order records, unique orders, or sale lines.

For example, if the orders table has one row per order:

SUM(amount) / COUNT(*)

calculates the average amount per order, provided the amount values and NULL handling match the intended definition.

If the table has multiple lines per order, COUNT(*) counts lines rather than orders, so it may not calculate average order value correctly.

Always identify the grain of the source table before interpreting a ratio.

NULL values can change report results.

COUNT(*)
Counts every source row.

COUNT(amount)
Counts only rows where amount is not NULL.

SUM(amount)
Adds non-NULL amounts. It returns NULL if every amount in the group is NULL.

AVG(amount)
Calculates the average of non-NULL amounts.

MIN(amount) and MAX(amount)
Ignore NULL inputs and return NULL if no non-NULL value is available.

Suppose a category has amounts 100, 200, and NULL.

COUNT(*) = 3
COUNT(amount) = 2
SUM(amount) = 300
AVG(amount) = 150

The average is 150 because AVG ignores the NULL value. It does not treat the missing amount as zero.

If missing amounts should count as zero in a particular business calculation, use an explicit expression such as AVG(COALESCE(amount, 0)) only when that definition is appropriate.

Joining tables before aggregation can multiply rows.

Suppose:
- orders has one row per order.
- order_items has multiple rows per order.

If an order worth 500 has three item rows, joining the order to its items produces three joined rows for that order.

Then this query may overstate revenue:

SELECT SUM(o.amount)
FROM orders AS o
JOIN order_items AS i
ON i.order_id = o.order_id;

The order amount is repeated once per matching item row.

The correct solution depends on the data model and the required metric. Options include:
- Aggregate order-level amounts before joining to item-level data.
- Calculate revenue from item quantities and prices when those represent the source of truth.
- Use separate subqueries or CTEs to summarize each table at the correct grain before combining results.

Do not assume that SUM(DISTINCT o.amount) is a general solution. Two different orders can legitimately have the same amount, and DISTINCT would incorrectly count that amount only once.

A useful reporting query can combine:
- WHERE to select the relevant source rows.
- GROUP BY to define report groups.
- Multiple aggregates to calculate metrics.
- HAVING to remove groups that do not meet a threshold.
- ORDER BY to rank the remaining groups.

Example requirement:
Show completed-order categories with at least five orders and at least 2,000 in total sales. Include the average amount, minimum amount, and maximum amount, then sort by total sales.

Correctness comes first, but reporting queries should also be efficient.

Useful practices:
1. Filter source rows with WHERE when the condition is part of the report definition.
2. Select only the columns the report needs.
3. Verify join relationships before calculating aggregates.
4. Check whether the grouping dimensions match the intended report grain.
5. Use EXPLAIN to inspect the PostgreSQL plan.
6. Use EXPLAIN ANALYZE when actual execution is safe and appropriate.
7. Test against realistic data volumes and distributions.

Indexes can help PostgreSQL find rows for some filters and joins, but an index does not guarantee a faster aggregate query. The planner may prefer a sequential scan or another strategy.

For large reports, consider whether the same summary is recalculated frequently. Depending on freshness requirements, a materialized view or precomputed summary table may be appropriate.

Do not add indexes without understanding the workload: indexes consume storage and can increase the cost of writes.

Assume these PostgreSQL tables exist:

orders(order_id, customer_id, category, status, amount)

sales(sale_id, department, category, quantity, unit_price)

Write a query for each task.

Task 1:
For each category, return the number of orders and total sales.

Task 2:
For each category, return the count, total, average, minimum, and maximum amount.

Task 3:
Return only completed-order categories with at least three orders.

Task 4:
Return completed-order categories with total sales above 2,000, sorted from highest to lowest total.

Task 5:
For each department and category, calculate the total revenue from quantity * unit_price.

Task 6:
Calculate each category's percentage contribution to total completed-order sales.

Task 7:
Explain the difference between COUNT(*) and COUNT(amount).

Task 8:
Explain why SUM(DISTINCT amount) is not a reliable general fix for inflated totals after a one-to-many join.

Task 9:
Explain what happens when every amount in a group is NULL.

Task 10:
Explain why GROUP BY category, status can return more rows than GROUP BY category.

Try to solve these before looking at the solutions.

Task 1 — Count orders and total sales:

SELECT
category,
COUNT(*) AS order_count,
SUM(amount) AS total_sales
FROM orders
GROUP BY category;

Task 2 — Calculate five aggregate metrics:

SELECT
category,
COUNT(*) AS order_count,
SUM(amount) AS total_sales,
AVG(amount) AS average_amount,
MIN(amount) AS minimum_amount,
MAX(amount) AS maximum_amount
FROM orders
GROUP BY category;

Task 3 — Completed-order categories with at least three orders:

SELECT
category,
COUNT(*) AS completed_order_count
FROM orders
WHERE status = 'Completed'
GROUP BY category
HAVING COUNT(*) >= 3;

Task 4 — Completed-order totals above 2,000:

SELECT
category,
SUM(amount) AS total_sales
FROM orders
WHERE status = 'Completed'
GROUP BY category
HAVING SUM(amount) > 2000
ORDER BY total_sales DESC;

Task 5 — Revenue by department and category:

SELECT
department,
category,
SUM(quantity * unit_price) AS total_revenue
FROM sales
GROUP BY department, category
ORDER BY total_revenue DESC NULLS LAST;

Task 6 — Percentage contribution:

WITH category_totals AS (
SELECT
category,
SUM(amount) AS total_sales
FROM orders
WHERE status = 'Completed'
GROUP BY category
)
SELECT
category,
total_sales,
ROUND(
100.0 * total_sales
/ NULLIF(SUM(total_sales) OVER (), 0),
2
) AS sales_percentage
FROM category_totals
ORDER BY total_sales DESC NULLS LAST;

Task 7:
COUNT(*) counts all rows. COUNT(amount) counts rows whose amount is not NULL.

Task 8:
Distinct amounts from different orders can be equal. SUM(DISTINCT amount) would count each different amount only once, potentially undercounting valid orders.

Task 9:
SUM(amount) returns NULL if every amount in the group is NULL. COUNT(*) still counts the rows.

Task 10:
Grouping by category and status creates a separate group for every distinct combination of those values, so one category can appear in multiple rows.

Choose the best answer for each question.

Q1. What is aggregate reporting?
Answer: Aggregate reporting summarizes source rows using functions such as COUNT, SUM, AVG, MIN, and MAX.

Q2. Can multiple aggregate functions be used in one SELECT query?
Answer: Yes. They can calculate different metrics for the same group.

Q3. What does GROUP BY determine?
Answer: It determines which rows are combined into each output group.

Q4. What is a multi-dimensional report?
Answer: A report grouped by multiple dimensions, such as department and category.

Q5. What is the difference between COUNT(*) and COUNT(column)?
Answer: COUNT(*) counts all rows, while COUNT(column) ignores NULL values in that column.

Q6. What is the difference between SUM and AVG?
Answer: SUM adds non-NULL values; AVG calculates their average.

Q7. What does HAVING do?
Answer: It filters groups based on conditions, often involving aggregates.

Q8. Can WHERE and HAVING be used together?
Answer: Yes. WHERE filters source rows, and HAVING filters groups.

Q9. How do you sort groups by revenue?
Answer: Use ORDER BY on the revenue aggregate or its SELECT alias.

Q10. Does GROUP BY sort results?
Answer: No. Use ORDER BY when the output order matters.

Q11. How do you calculate percentage contribution?
Answer: Divide the group's value by the relevant overall total and multiply by 100.

Q12. Why use NULLIF in a percentage calculation?
Answer: It converts a zero denominator to NULL, preventing division by zero.

Q13. Why can integer division cause incorrect percentages?
Answer: It may discard the fractional part. Use appropriate numeric arithmetic, such as multiplying by 100.0.

Q14. How does SUM handle NULL?
Answer: It ignores NULL inputs and returns NULL if all inputs are NULL.

Q15. What is the danger of grouping by too many columns?
Answer: It can produce more detailed groups than intended and change the report's meaning.

Q16. Why can joins inflate aggregate values?
Answer: One-to-many relationships can repeat parent rows in the joined result.

Q17. Why is SUM(DISTINCT amount) not a general fix for double-counting?
Answer: Separate legitimate transactions can have equal amounts, so DISTINCT may remove valid contributions.

Q18. What is the purpose of a CTE in a reporting query?
Answer: It can separate complex calculations into named, readable steps.

Q19. What does SUM(total_sales) OVER () do after category aggregation?
Answer: It calculates the sum across the category rows while retaining each row's individual total.

Q20. How can you investigate a slow reporting query?
Answer: Inspect the PostgreSQL execution plan, joins, filters, grouping operations, indexes, and data volume.

Q21. Why must a report define its source table's grain?
Answer: It determines whether a row represents an order, order line, customer, or another entity, which affects counts and ratios.

Q22. What is the difference between a row count and a distinct order count?
Answer: COUNT(*) counts source rows; COUNT(DISTINCT order_id) counts distinct non-NULL order IDs.

Q23. What happens if a grouped query receives no source rows?
Answer: With GROUP BY, it returns no groups. An aggregate query without GROUP BY can still return one row with aggregate values such as COUNT(*) = 0 and SUM(column) = NULL.

Q24. When might a materialized view help?
Answer: It may help when expensive summaries are requested repeatedly and the application can tolerate the view's refresh strategy.

Q25. What should you verify before publishing a sales report?
Answer: Confirm the business definitions, grouping dimensions, filters, join cardinality, NULL handling, denominator, and ordering.

You have learned how to build richer SQL reports by combining grouping, aggregation, filtering, sorting, and percentage calculations.

Remember:

- GROUP BY defines each output group.
- Multiple aggregates can summarize the same group.
- COUNT(*) counts rows; COUNT(column) counts non-NULL values.
- SUM, AVG, MIN, and MAX have their own NULL behavior.
- Additional grouping columns produce more detailed groups.
- WHERE filters input rows.
- HAVING filters groups after aggregation.
- ORDER BY sorts the report.
- Percentage contribution requires the correct overall denominator.
- NULLIF protects division when the denominator is zero.
- One-to-many joins can multiply rows and inflate totals.
- CTEs can make complex reporting calculations easier to follow.
- EXPLAIN helps investigate performance, but indexes do not guarantee a faster plan.

Before finishing a report, ask:

1. What does one source row represent?
2. What should one output row represent?
3. Which source rows should be included?
4. Which aggregates are required?
5. Which groups should be retained?
6. What is the correct percentage denominator?
7. Can any join multiply rows?
8. Is a particular result order required?

A correct report is not just syntactically valid SQL. Its calculations must match the business question.

Avoid these common errors when combining aggregates and grouping.