SQL Aggregate Window Functions: COUNT, SUM, AVG, MIN, and MAX

In standard SQL, aggregate functions collapse multiple rows into a single scalar value when paired with a GROUP BY clause. While this is useful for high-level summaries, it destroys underlying row-level details.

Aggregate Window Functions bridge this gap by computing an aggregated value over a defined subset (or window) of data while preserving every original row in the result set. This chapter explores the mechanics, syntax rules, null-handling behaviors, and production analytics use cases for the five core aggregate window functions: COUNT, SUM, AVG, MIN, and MAX.

1. Core Mental Model: Standard Aggregation vs. Window Aggregation

Standard aggregations force a choice between showing high-level macro totals or preserving granular individual transactions. Aggregate window functions allow you to present both simultaneously in the same query.

Execution Flow of a Window Aggregation

2. Syntax Architecture and Window Anatomy

All five aggregate window functions follow a standardized syntax structure:

FUNCTION_NAME(expression) OVER (
    [PARTITION BY partition_column]
    [ORDER BY sort_column [ASC | DESC]]
    [ROWS BETWEEN frame_start AND frame_end]
)

Expression and Clause Rules Across Functions

Note: If the parentheses inside OVER() are left completely empty (OVER ()), SQL treats the entire result set as a single, undivided window frame.

3. Deep Dive into COUNT(): Mechanics, Null Sensitivity, and Diagnostics

The COUNT() window function returns the number of rows or non-null values within a partition. It is the only aggregate function that accepts any data type as its argument.

COUNT(*) vs. COUNT(column_name)

The argument passed to COUNT() dictates how NULL values are evaluated:

• COUNT(*) or COUNT(1): Counts every physical row inside the window, regardless of whether columns contain NULLs.
• COUNT(column_name): Evaluates only the specified column and ignores NULL values.

SELECT 
    order_id,
    product,
    sales,
    COUNT(*) OVER (PARTITION BY product) AS count_star,
    COUNT(1) OVER (PARTITION BY product) AS count_one,
    COUNT(sales) OVER (PARTITION BY product) AS count_sales
FROM sales_orders;

Evaluation Breakdown

Assume the Gloves partition contains three rows with sales: 30, 70, and NULL.

count_star and count_one return identical results and have virtually indistinguishable performance characteristics in modern SQL engines.

Use Case: Data Quality and Completeness Audits

By evaluating COUNT(*) against COUNT(column_name) across the entire table, you can instantly detect missing values without manually inspecting every record.

SELECT 
    customer_id,
    first_name,
    country,
    score,
    COUNT(*) OVER () AS total_customers,
    COUNT(country) OVER () AS total_valid_countries,
    COUNT(score) OVER () AS total_valid_scores
FROM sales_customers;

4. Production Data Quality Pattern: Detecting Primary Key Duplicates

In production data engineering pipelines, data staging tables often lack strict database constraints (like primary key unique indexes). To prevent inaccurate downstream aggregations, aggregate window functions can be used to audit primary key uniqueness.

Detecting and Isolating Duplicates

Because SQL does not allow window functions directly within the WHERE clause, a subquery (or Common Table Expression) is required to filter the results.

-- Step 1 & 2: Wrap window count in a subquery and filter for anomalies
SELECT 
    order_id,
    order_date,
    customer_id,
    check_pk
FROM (
    SELECT 
        order_id,
        order_date,
        customer_id,
        COUNT(*) OVER (PARTITION BY order_id) AS check_pk
    FROM sales_orders_archive
) AS audited_orders
WHERE check_pk > 1;

If check_pk returns 1 for all rows, the candidate primary key is clean. Any value greater than 1 isolates the exact duplicate rows that violate the data model.

5. SUM() and Part-to-Whole Contribution Analysis

The SUM() function calculates the cumulative total of a numeric expression across a partition. It silently ignores NULL values during calculation.

SELECT 
    order_id,
    order_date,
    product_id,
    sales,
    -- Macro Metric: Total sales across all records
    SUM(sales) OVER () AS total_sales,
    -- Micro Metric: Total sales for this specific product
    SUM(sales) OVER (PARTITION BY product_id) AS sales_by_product
FROM sales_orders;

Part-to-Whole Percentage Calculation

A classic analytical use case is Part-to-Whole Analysis, which measures the relative contribution of an individual transaction to the overall revenue.

Production Pitfall: Integer Division

If sales is stored as an integer, dividing sales by SUM(sales) will result in integer truncation (returning 0). You must cast the numerator or denominator to a floating-point numeric type.

SELECT 
    order_id,
    product_id,
    sales,
    SUM(sales) OVER () AS total_sales,
    -- Explicit cast avoids integer division truncation
    ROUND(
        (CAST(sales AS FLOAT) / SUM(sales) OVER ()) * 100, 
        2
    ) AS pct_of_total
FROM sales_orders;

6. AVG(): Arithmetic Nuances and the Business Meaning of NULL

The AVG() function calculates the arithmetic mean for values in a window partition.

The NULL Division Trap

By default, SQL's AVG() function ignores NULLs in both the numerator and denominator:

Sample Window: [30, 70, NULL]
Default AVG() calculation: (30 + 70) / 2 = 50.0

If the business context dictates that a NULL represents zero activity (e.g., zero sales or an unearned score), calculating AVG() without handling NULLs artificially inflates the metric.

Expected Business Calculation: (30 + 70 + 0) / 3 = 33.33

To align the metric with business logic, use COALESCE() to replace NULL values with 0 before the average is calculated.

SELECT 
    customer_id,
    last_name,
    score,
    -- Ignores NULLs (Denominator = 4)
    AVG(score) OVER () AS avg_score_ignore_nulls,
    -- Treats NULL as 0 (Denominator = 5)
    AVG(COALESCE(score, 0)) OVER () AS avg_score_with_zeroes
FROM sales_customers;

Outlier & Threshold Filtering with AVG()

To identify transactions that outperform the macro average, compute the benchmark average across the entire dataset, then filter using an outer query:

SELECT 
    order_id,
    product_id,
    sales,
    benchmark_avg
FROM (
    SELECT 
        order_id,
        product_id,
        sales,
        AVG(sales) OVER () AS benchmark_avg
    FROM sales_orders
) AS evaluated_sales
WHERE sales > benchmark_avg;

7. MIN() and MAX(): Range Bounds and Deviation from Extremes

MIN() and MAX() return the lowest and highest values within a window partition, respectively. Like other arithmetic functions, they ignore NULLs.

SELECT 
    order_id,
    product_id,
    sales,
    -- Global extremes
    MAX(sales) OVER () AS max_overall_sales,
    MIN(sales) OVER () AS min_overall_sales,
    -- Partition-specific extremes
    MAX(sales) OVER (PARTITION BY product_id) AS max_product_sales,
    MIN(sales) OVER (PARTITION BY product_id) AS min_product_sales
FROM sales_orders;

Deviation Analysis from Extremes

Comparing individual data points to the partition's extreme boundaries helps evaluate variance and distance from performance ceilings or floors.

SELECT 
    order_id,
    product_id,
    sales,
    MIN(sales) OVER () AS min_sales,
    MAX(sales) OVER () AS max_sales,
    -- Distance from the bottom boundary
    sales - MIN(sales) OVER () AS deviation_from_min,
    -- Distance from the top boundary
    MAX(sales) OVER () - sales AS deviation_from_max
FROM sales_orders;

8. Time-Series Aggregations: Running Totals vs. Rolling Totals

When an ORDER BY clause is added to an aggregate window function, SQL converts the static partition aggregate into a dynamic, time-series calculation.

The Mechanism: Default Frame vs. Explicit Frame

Whenever an ORDER BY clause is present without an explicit frame clause, SQL applies a default frame:

ROWS BETWEEN UNBOUNDED PRECEDING AND CURRENT ROW

This default creates an expanding running total. To create a fixed rolling total, you must explicitly override the frame definition.

SELECT 
    month,
    sales,
    -- Running Total (Expanding Window)
    SUM(sales) OVER (
        ORDER BY month 
        ROWS BETWEEN UNBOUNDED PRECEDING AND CURRENT ROW
    ) AS running_total,
    -- 3-Month Rolling Total (Fixed Sliding Window)
    SUM(sales) OVER (
        ORDER BY month 
        ROWS BETWEEN 2 PRECEDING AND CURRENT ROW
    ) AS rolling_3_month_total
FROM monthly_sales;

Frame Execution Trace

9. Moving Averages & Custom Sliding Window Frames

Just as SUM() can be combined with ORDER BY to compute running and rolling totals, AVG() can be used to generate moving averages that smooth out short-term fluctuations in time-series data.

SELECT 
    order_id,
    product_id,
    order_date,
    sales,
    -- Moving Average (Expanding cumulative average per product over time)
    AVG(sales) OVER (
        PARTITION BY product_id 
        ORDER BY order_date ASC
    ) AS expanding_moving_avg,
    -- Rolling 2-Order Forward Average (Current transaction + 1 following transaction)
    AVG(sales) OVER (
        PARTITION BY product_id 
        ORDER BY order_date ASC
        ROWS BETWEEN CURRENT ROW AND 1 FOLLOWING
    ) AS rolling_forward_avg
FROM sales_orders;

Partition Boundary Isolation

Window calculations respect partition boundaries. When the query moves to a new product_id, the frame resets automatically:

10. The Analytical Progression Framework (From Macro to Micro)

By adjusting the components of the OVER() clause, a single aggregate function can answer a wide range of analytical questions:

Summary and Key Takeaways

• Preservation of Granular Detail: Unlike standard GROUP BY clauses, aggregate window functions compute macro-level metrics while retaining all individual rows in the output.
• Data Type Rules: COUNT() accepts any valid expression, column type, literal (1), or *. In contrast, SUM() and AVG() require numeric arguments (or expressions cast to numeric types).
• Nuances of NULL Handling: COUNT(*) counts all physical rows, while COUNT(column) ignores NULLs. This difference provides a quick way to audit data quality and missing values.
• Handling Zeroes vs. NULLs in AVG(): AVG() omits NULL values from both the sum and the row count. Use COALESCE(column, 0) when a NULL represents a zero value to prevent inflated results.
• Filtering via Subqueries / CTEs: Because window functions cannot appear in WHERE or HAVING clauses, you must wrap windowed calculations in subqueries or CTEs to filter on their results.
• Preventing Integer Truncation: When calculating ratios or percentages (such as part-to-whole analyses), always cast integer fields to a floating-point type (CAST(col AS FLOAT)) before dividing.
• Running vs. Rolling Mechanics: Adding ORDER BY to a window function creates an expanding cumulative calculation by default (ROWS BETWEEN UNBOUNDED PRECEDING AND CURRENT ROW). To calculate fixed, sliding metrics (such as a 3-month rolling total), specify an explicit frame like ROWS BETWEEN 2 PRECEDING AND CURRENT ROW.