Aggregation and Analytical SQL

Introduction

Transactional queries retrieve individual rows, but managerial reporting usually asks summary questions: What were total sales by region? Which products exceeded a target? How does each employee’s salary compare with the departmental average? SQL answers these questions through aggregate functions, grouping, filtering of groups, grouping sets, and window functions.

Aggregation transforms a relation containing many detail rows into a smaller relation containing descriptive statistics. Analytical SQL extends this capability by calculating summaries while preserving the detail rows needed for comparison and interpretation. These techniques support dashboards, KPI systems, financial reports, operational monitoring, and evidence-based decision-making.

The examples use a conventional sales schema:

• sales(order_id, order_date, customer_id, salesperson_id, region, product_id, quantity, unit_price)
• products(product_id, product_name, category)
• employees(employee_id, employee_name, department, salary)

SQL syntax varies across PostgreSQL, MySQL, SQL Server, Oracle, and other systems. The logical principles are portable, but date functions, identifier quoting, and some grouping features may differ.

Learning Objectives

By the end of this chapter, you should be able to:

1. Apply COUNT, SUM, AVG, MIN, and MAX correctly.
2. Explain the logical processing order of grouped queries.
3. Construct reports using GROUP BY and filter groups using HAVING.
4. Use conditional aggregation for KPIs and ratios.
5. Produce subtotals and grand totals with grouping sets.
6. Distinguish grouped aggregation from window functions.
7. Use ranking, running totals, moving averages, and percentage-of-total calculations.
8. Evaluate null handling, precision, performance, and governance issues in analytical SQL.

1. Aggregate Functions and Their Semantics

An aggregate function receives values from multiple rows and returns one value for a group. Common functions are shown below.

For a set of non-null values x₁, x₂, …, xₙ, the arithmetic mean is:

The denominator is the number of non-null observations, not necessarily the number of rows. This distinction matters when missing values have meaning.

Basic examples

SELECT
    COUNT(*) AS order_count,
    COUNT(DISTINCT customer_id) AS customer_count,
    SUM(quantity) AS units_sold,
    AVG(unit_price) AS average_unit_price,
    MIN(order_date) AS first_order,
    MAX(order_date) AS last_order
FROM sales;

The expression quantity * unit_price represents line revenue when each row is a sales line and unit_price is the price per unit:

SELECT SUM(quantity * unit_price) AS gross_revenue
FROM sales;

This calculation assumes that discounts, taxes, returns, and currency conversions are either absent or handled elsewhere. A production revenue metric must define these business rules explicitly.

2. Grouping Rows with GROUP BY

GROUP BY partitions rows into groups having equal values for the specified expressions. An aggregate is then calculated independently within each group.

SELECT
    region,
    SUM(quantity * unit_price) AS revenue,
    COUNT(*) AS order_lines
FROM sales
GROUP BY region
ORDER BY revenue DESC;

Every selected expression must either be:

• included in the GROUP BY list; or
• reduced by an aggregate function.

Thus, the following is invalid in standard SQL because product_id is neither grouped nor aggregated:

-- Invalid in standard SQL
SELECT region, product_id, SUM(quantity * unit_price)
FROM sales
GROUP BY region;

A valid alternative groups by both attributes:

SELECT region, product_id, SUM(quantity * unit_price) AS revenue
FROM sales
GROUP BY region, product_id;

Logical processing order

Although SQL is written beginning with SELECT, its conceptual processing order is approximately:

1. FROM and JOIN
2. WHERE
3. GROUP BY
4. Aggregate calculations
5. HAVING
6. Window functions (conceptually after grouping)
7. SELECT
8. ORDER BY
9. LIMIT or equivalent

This explains why a WHERE clause cannot normally contain an aggregate such as SUM(quantity): WHERE filters rows before groups exist. Group-level conditions belong in HAVING.

3. WHERE and HAVING

WHERE removes detail rows before aggregation. HAVING removes completed groups after aggregation.

SELECT region, SUM(quantity * unit_price) AS revenue
FROM sales
WHERE order_date >= DATE '2025-01-01'
GROUP BY region
HAVING SUM(quantity * unit_price) >= 100000;

The query first restricts sales to 2025, then creates regional groups, calculates revenue, and retains regions meeting the threshold. Date-literal syntax is not identical in every DBMS; use the form recommended by the target platform.

A condition involving only row-level attributes normally belongs in WHERE, because early filtering reduces data volume and can improve performance:

WHERE quantity > 0

A condition involving an aggregate belongs in HAVING:

HAVING COUNT(DISTINCT customer_id) >= 50

4. Conditional Aggregation and KPI Design

Conditional aggregation calculates several measures in one pass using a conditional expression.

SELECT
    region,
    SUM(quantity * unit_price) AS total_revenue,
    SUM(CASE WHEN quantity * unit_price >= 1000
             THEN quantity * unit_price ELSE 0 END) AS large_line_revenue,
    COUNT(CASE WHEN quantity * unit_price >= 1000
               THEN 1 END) AS large_line_count
FROM sales
GROUP BY region;

A KPI is meaningful only when its numerator, denominator, population, time period, and exclusions are defined. For example, revenue per order is:

If sales contains one row per line rather than one row per order, the denominator must be COUNT(DISTINCT order_id), not COUNT(*).

Ratios and integer division

Some DBMSs perform integer division when both operands are integers. To obtain a percentage portably, cast an operand to a decimal type or multiply before division:

SELECT
    region,
    100.0 * SUM(CASE WHEN quantity > 0 THEN 1 ELSE 0 END)
        / NULLIF(COUNT(*), 0) AS positive_line_percentage
FROM sales
GROUP BY region;

NULLIF(denominator, 0) prevents division by zero by returning NULL when the denominator is zero. The expression calculates the percentage of rows with positive quantity, assuming each row is the intended observation. If the business unit is an order, use distinct orders instead.

A ratio can be represented generally as:

The numerator and denominator must describe compatible populations and units. Multiplying by 100.0 or casting to a decimal helps avoid truncation.

5. Grouping Sets, ROLLUP, and CUBE

Reports often require detail summaries, subtotals, and grand totals. Repeating separate queries with UNION ALL is possible but verbose. Grouping sets express multiple grouping levels in one query.

SELECT
    region,
    category,
    SUM(s.quantity * s.unit_price) AS revenue
FROM sales AS s
JOIN products AS p ON p.product_id = s.product_id
GROUP BY GROUPING SETS (
    (region, category),
    (region),
    (category),
    ()
);

The empty grouping set () represents the grand total. Nulls in the output may mean either a genuine null attribute or a subtotal placeholder. Many DBMSs provide GROUPING(region) to distinguish these cases:

SELECT
    region,
    category,
    GROUPING(region) AS region_is_total,
    GROUPING(category) AS category_is_total,
    SUM(s.quantity * s.unit_price) AS revenue
FROM sales AS s
JOIN products AS p ON p.product_id = s.product_id
GROUP BY GROUPING SETS ((region, category), (region), (category), ());

ROLLUP(region, category) normally produces hierarchical levels such as (region, category), (region), and (). It is appropriate for geographic or organizational hierarchies. CUBE(region, category) produces all combinations: detail, each single dimension, and grand total. Because CUBE can generate many groups, it should be used carefully.

Grouping sets reduce repeated scans conceptually, but actual performance depends on the optimizer, indexes, data volume, and DBMS implementation.

6. Window Functions: Analysis Without Losing Detail

A window function calculates across a related set of rows while retaining one output row for each input row. Its general structure is:

function(value) OVER (
    PARTITION BY grouping_columns
    ORDER BY ordering_columns
    frame_definition
)

• PARTITION BY divides rows into independent analytical groups.
• ORDER BY establishes sequence within each partition.
• A frame defines the rows used relative to the current row.

Grouped aggregation versus windows

Grouped aggregation reduces rows:

SELECT region, SUM(quantity * unit_price) AS regional_revenue
FROM sales
GROUP BY region;

A window aggregate preserves rows:

SELECT
    order_id,
    region,
    quantity * unit_price AS line_revenue,
    SUM(quantity * unit_price) OVER (PARTITION BY region) AS regional_revenue
FROM sales;

The second query allows each line to be compared with its regional total.

Ranking

SELECT
    product_id,
    SUM(quantity * unit_price) AS revenue,
    RANK() OVER (ORDER BY SUM(quantity * unit_price) DESC) AS revenue_rank
FROM sales
GROUP BY product_id;

RANK gives tied rows the same rank and leaves gaps after ties. DENSE_RANK also gives ties the same rank but does not leave gaps. ROW_NUMBER assigns a unique sequence, so its result requires a deterministic tie-breaker:

ROW_NUMBER() OVER (ORDER BY revenue DESC, product_id)

Some DBMSs do not allow a select-list alias such as revenue inside the same window definition. A derived table or common table expression makes the query more portable:

WITH product_totals AS (
    SELECT product_id, SUM(quantity * unit_price) AS revenue
    FROM sales
    GROUP BY product_id
)
SELECT
    product_id,
    revenue,
    RANK() OVER (ORDER BY revenue DESC) AS revenue_rank
FROM product_totals;

Running totals and moving averages

WITH daily_sales AS (
    SELECT order_date, SUM(quantity * unit_price) AS daily_revenue
    FROM sales
    GROUP BY order_date
)
SELECT
    order_date,
    daily_revenue,
    SUM(daily_revenue) OVER (
        ORDER BY order_date
        ROWS BETWEEN UNBOUNDED PRECEDING AND CURRENT ROW
    ) AS cumulative_revenue,
    AVG(daily_revenue) OVER (
        ORDER BY order_date
        ROWS BETWEEN 6 PRECEDING AND CURRENT ROW
    ) AS seven_row_average
FROM daily_sales;

The seven-row average is not necessarily a seven-calendar-day average if dates are missing. A true calendar-based interval requires DBMS-specific range-frame support or a calendar table. This is an important distinction between row-based and time-based windows.

Percentage of a partition total

SELECT
    region,
    product_id,
    product_revenue,
    100.0 * product_revenue
        / NULLIF(SUM(product_revenue) OVER (PARTITION BY region), 0)
        AS region_share_percentage
FROM product_region_totals;

The 100.0 ensures decimal arithmetic. The denominator is the regional total, so the shares within each region should sum approximately to 100%, subject to rounding and null treatment.

7. Worked Application: Retail Performance Report

Background

An illustrative online retailer wants a monthly report showing regional revenue, order volume, average order value, contribution to the monthly total, and rank among regions.

Problem definition

The report must:

1. include only completed orders;
2. calculate revenue as quantity multiplied by unit price;
3. count distinct orders rather than line items;
4. avoid integer truncation in percentages; and
5. rank regions within each month.

Assume an orders table with status, and a related sales table containing order lines. A portable design first aggregates to the required grain—month, region, and order—before calculating regional metrics:

WITH order_totals AS (
    SELECT
        s.order_id,
        CAST(s.order_date AS DATE) AS order_date,
        s.region,
        SUM(s.quantity * s.unit_price) AS order_revenue
    FROM sales AS s
    JOIN orders AS o ON o.order_id = s.order_id
    WHERE o.status = 'Completed'
    GROUP BY s.order_id, CAST(s.order_date AS DATE), s.region
),
monthly_region AS (
    SELECT
        EXTRACT(YEAR FROM order_date) AS sales_year,
        EXTRACT(MONTH FROM order_date) AS sales_month,
        region,
        COUNT(*) AS order_count,
        SUM(order_revenue) AS revenue,
        AVG(order_revenue) AS average_order_value
    FROM order_totals
    GROUP BY EXTRACT(YEAR FROM order_date),
             EXTRACT(MONTH FROM order_date), region
)
SELECT
    sales_year,
    sales_month,
    region,
    order_count,
    revenue,
    average_order_value,
    100.0 * revenue
        / NULLIF(SUM(revenue) OVER (PARTITION BY sales_year, sales_month), 0)
        AS monthly_share_percentage,
    RANK() OVER (
        PARTITION BY sales_year, sales_month
        ORDER BY revenue DESC
    ) AS regional_rank
FROM monthly_region
ORDER BY sales_year, sales_month, regional_rank, region;

Analysis

The first CTE establishes the order grain. This prevents a common error: averaging line revenue or counting lines when the KPI concerns orders. The second CTE creates monthly regional groups. The final query adds two window calculations without collapsing the regional rows.

For example, if one month contains regional revenues of 12,000, 8,000, and 5,000, total revenue is 25,000. The first region’s share is:

The result supports decisions about regional investment, sales staffing, and campaign effectiveness. It does not by itself establish profitability, because costs and margins are absent.

8. Data Quality, Performance, and Governance

Nulls and missing data

Null is not the same as zero. SUM ignores null values, while replacing null with zero using COALESCE changes the analytical meaning. Use COALESCE only when the business interpretation supports treating missing values as zero.

Grain and double counting

Joining two one-to-many tables before aggregation can multiply rows. For example, joining order lines to multiple payment records may duplicate revenue. Aggregate each source to a compatible grain before joining, or use a controlled dimensional model.

Precision and rounding

Currency calculations should use exact decimal types where supported, not floating-point types. Round for presentation at the final stage unless accounting policy requires line-level rounding. Percentages should retain sufficient precision internally and be rounded only in the displayed result.

Performance

Useful practices include:

• filter early with selective predicates;
• index join and filter columns where appropriate;
• inspect execution plans;
• pre-aggregate large fact tables when the reporting grain is stable;
• avoid COUNT(DISTINCT ...) unnecessarily on very large data sets;
• use partitioning or materialized views for recurring time-based reports;
• verify that window ordering is supported by an efficient plan.

Performance improvements must preserve metric correctness. A faster query that double-counts revenue is not an improvement.

Governance and ethics

Analytical SQL can influence bonuses, credit decisions, staffing, and customer treatment. Organizations should document KPI definitions, maintain lineage from source columns to published metrics, restrict sensitive data, and audit access. Analysts should disclose missing data, filters, time zones, and exclusions. Ranking individuals without accounting for role, territory, workload, or data quality can create unfair conclusions.

9. Contemporary Extensions

Modern cloud warehouses commonly support columnar storage, distributed aggregation, materialized views, approximate distinct-count functions, and semantic layers that standardize metrics. These features can improve scale, but approximate functions trade exactness for speed and must be labeled accordingly.

AI-assisted SQL generation can suggest queries and explain execution plans, but generated SQL must be reviewed for join multiplication, incorrect grain, privacy exposure, dialect incompatibility, and integer division. Human ownership of definitions and validation remains essential.

Chapter Summary

Aggregate functions summarize rows using measures such as count, sum, average, minimum, and maximum. GROUP BY creates groups, while WHERE filters rows before grouping and HAVING filters groups afterward. Conditional aggregation supports KPI construction, but ratios require carefully matched populations, protection against zero denominators, and decimal arithmetic to avoid integer truncation.

Grouping sets, ROLLUP, and CUBE produce detail rows, subtotals, and grand totals. Window functions differ from grouped aggregation because they calculate across related rows while preserving row detail. Ranking, running totals, moving averages, and percentage-of-total metrics are built with partitions, orderings, and explicit frames. Reliable analytical SQL depends on correct data grain, null interpretation, precision, performance testing, documented definitions, and responsible governance.

Key Terms

Aggregate function: A function that reduces multiple values to one result.

COUNT(*): Counts rows in the input relation.

COUNT(DISTINCT): Counts unique non-null values.

GROUP BY: Partitions rows into groups for aggregation.

HAVING: Filters groups after aggregation.

Conditional aggregation: Aggregation performed over values satisfying a condition.

KPI: A defined measure used to monitor performance against an objective.

Grouping set: An explicitly specified grouping level in a summary query.

ROLLUP: Generates hierarchical subtotals and a grand total.

CUBE: Generates combinations of subtotals across dimensions.

Window function: A function that analyzes related rows without collapsing them.

Partition: A subset of rows processed independently by a window function.

Window frame: The rows included in a window calculation for the current row.

RANK: Ranking function that leaves gaps after ties.

DENSE_RANK: Ranking function that does not leave gaps after ties.

Running total: A cumulative aggregate ordered over a sequence.

Integer division: Division that discards a fractional part when operands are integers in some DBMSs.

Data grain: The real-world unit represented by one row.

Metric lineage: Documentation connecting a reported metric to its source data and transformations.

Review Questions

Short Answer Questions

1. What is the difference between COUNT(*) and COUNT(column)?
2. Why is an aggregate condition usually placed in HAVING rather than WHERE?
3. What does the empty grouping set () represent?
4. How does a window function differ from a grouped aggregate?
5. Why should an operand be cast to decimal when calculating a percentage?

Long Answer Questions

1. Explain the logical processing order of a grouped SQL query and illustrate it with a regional revenue example.
2. Compare GROUPING SETS, ROLLUP, and CUBE, including suitable business uses and possible risks.
3. Explain how incorrect data grain can cause double counting in joins.
4. Discuss the difference between RANK, DENSE_RANK, and ROW_NUMBER.
5. Design a governance policy for KPI definitions produced by analytical SQL.

Analytical / Application Questions

1. A sales table contains multiple lines per order. Write a query strategy for calculating average order value by month without treating each line as an order.
2. A seven-row moving average is used on daily sales, but weekends have no rows. Explain why the result may not be a seven-day average and propose a solution.
3. A regional percentage report produces 0% for many groups even though the numerator is nonzero. Diagnose the likely SQL problem and show how to correct it portably.

Multiple Choice Questions with Answers

Question 1

Which expression counts all rows, including rows where a particular column is NULL?

A. COUNT(column_name)

B. COUNT(*)

C. COUNT(DISTINCT column_name)

D. SUM(column_name)

Correct Answer: B. COUNT(*)

Question 2

Which clause filters groups after aggregate calculations have been performed?

A. WHERE

B. ORDER BY

C. HAVING

D. FROM

Correct Answer: C. HAVING

Question 3

A table has one row per order line. Which expression counts distinct orders?

A. COUNT(*)

B. COUNT(quantity)

C. COUNT(DISTINCT order_id)

D. SUM(order_id)

Correct Answer: C. COUNT(DISTINCT order_id)

Question 4

Which SQL feature preserves detail rows while calculating a regional total beside each row?

A. GROUP BY only

B. A window aggregate with PARTITION BY

C. HAVING

D. DISTINCT only

Correct Answer: B. A window aggregate with PARTITION BY

Question 5

What does the empty grouping set () produce in a GROUPING SETS query?

A. One group for every row

B. A subtotal for each category

C. The grand total

D. No output rows

Correct Answer: C. The grand total

Question 6

Why is 100.0 commonly used instead of 100 in a percentage expression?

A. It forces decimal arithmetic in systems where integer division could truncate

B. It automatically removes NULL values

C. It creates a window partition

D. It sorts the result numerically

Correct Answer: A. It forces decimal arithmetic in systems where integer division could truncate

Question 7

Which ranking function assigns tied rows the same rank and leaves gaps after ties?

A. ROW_NUMBER

B. DENSE_RANK

C. RANK

D. NTILE

Correct Answer: C. RANK

Question 8

A report must calculate a cumulative total in date order. Which window frame is most directly appropriate?

A. ROWS BETWEEN UNBOUNDED PRECEDING AND CURRENT ROW

B. ROWS BETWEEN CURRENT ROW AND UNBOUNDED FOLLOWING

C. ROWS BETWEEN 1 FOLLOWING AND 2 FOLLOWING

D. No ORDER BY or frame

Correct Answer: A. ROWS BETWEEN UNBOUNDED PRECEDING AND CURRENT ROW

Question 9

A KPI is calculated as 12,000 ÷ 300. What is the result?

A. 4

B. 40

C. 400

D. 4,000

Correct Answer: B. 40

Question 10

Which issue can occur when a sales-line table is joined to another table containing multiple rows per order before aggregation?

A. Revenue may be duplicated by join multiplication

B. All aggregate functions become window functions

C. NULL values are automatically converted to zero

D. GROUP BY becomes unnecessary

Correct Answer: A. Revenue may be duplicated by join multiplication