Window SUM

Editorial

Introduction

The SUM window function is essential for financial analysis, cumulative calculations, and comparing individual values to totals. With SUM() OVER(), you can calculate totals while preserving every row of detail.

In this lesson, you will learn:

How to use SUM as a window function
Calculate running totals and cumulative sums
Partition sums by different dimensions
Practical use cases for financial and business analysis

Basic Syntax

The window SUM function follows this pattern:

SUM(column) OVER (
    [PARTITION BY column(s)]
    [ORDER BY column(s)]
    [frame_clause]
)

Key Behaviors

OVER() Contents	Result
Empty `OVER()`	Sum of all rows in result set
`PARTITION BY`	Sum within each partition
`ORDER BY`	Running (cumulative) sum
Both	Running sum within each partition

Visualization

Window SUM — running total, with SUM(revenue) OVER (ORDER BY month ROWS UNBOUNDED PRECEDING) AS running_total. Monthly revenue 2018-01: 100, 2018-02: 150, 2018-03: 200, 2018-04: 120, 2018-05: 180. The expanding window adds one month per row, drawn to scale: 100 = 100; 100 + 150 = 250; 100 + 150 + 200 = 450; + 120 = 570; + 180 = 750. UNBOUNDED PRECEDING includes all rows from start to current row.

Window SUM -- Running Totals and Cumulative Sums

SUM with PARTITION BY

PARTITION BY divides your data into groups and calculates a separate sum for each group.

Total Sales per Seller

-- Sum of sales per seller, shown on every item row
SELECT 
    seller_id,
    order_id,
    price,
    SUM(price) OVER (PARTITION BY seller_id) AS seller_total_sales
FROM olist_order_items_dataset
LIMIT 20;

Each row now shows the seller's total sales alongside the individual item price.

Total Order Value

-- Total value per order, shown on every item
SELECT 
    order_id,
    product_id,
    price,
    freight_value,
    SUM(price) OVER (PARTITION BY order_id) AS order_total_price,
    SUM(freight_value) OVER (PARTITION BY order_id) AS order_total_freight
FROM olist_order_items_dataset
LIMIT 20;

This shows each item with its order's total price and total freight.

SUM with Empty OVER()

When OVER() has no arguments, the sum includes all rows in the result set.

Grand Total on Every Row

-- Show grand total on every row
SELECT 
    order_id,
    price,
    SUM(price) OVER () AS grand_total
FROM olist_order_items_dataset
LIMIT 15;

Calculate Percentage of Total

This pattern is extremely useful for seeing each item's contribution to the whole:

-- Each item as percentage of total revenue
SELECT 
    order_id,
    seller_id,
    price,
    SUM(price) OVER () AS total_revenue,
    ROUND(100.0 * price / SUM(price) OVER (), 4) AS pct_of_total
FROM olist_order_items_dataset
LIMIT 15;

Percentage Within Groups

Combine both patterns to see percentage within each group:

-- Item as percentage of seller's total
SELECT 
    seller_id,
    order_id,
    price,
    SUM(price) OVER (PARTITION BY seller_id) AS seller_total,
    ROUND(100.0 * price / SUM(price) OVER (PARTITION BY seller_id), 2) AS pct_of_seller_total
FROM olist_order_items_dataset
LIMIT 20;

Running Sum (Cumulative Total)

Adding ORDER BY creates a running sum (also called cumulative sum). The sum accumulates as you move through the ordered rows.

Basic Running Total

-- Running total of prices
SELECT 
    order_id,
    price,
    SUM(price) OVER (ORDER BY order_id) AS running_total
FROM olist_order_items_dataset
LIMIT 15;

Notice how each row's running_total includes all previous prices plus the current price.

How Running Sum Works

Row 1: price = 100 → running = 100
Row 2: price = 50  → running = 100 + 50 = 150
Row 3: price = 75  → running = 150 + 75 = 225
Row 4: price = 200 → running = 225 + 200 = 425

Running Sum Within Partitions

Combine PARTITION BY and ORDER BY for running sums that restart for each group:

-- Running total per seller
SELECT 
    seller_id,
    order_id,
    price,
    SUM(price) OVER (
        PARTITION BY seller_id 
        ORDER BY order_id
    ) AS seller_running_total
FROM olist_order_items_dataset
LIMIT 20;

The running total resets to zero at the start of each new seller.

Combining SUM with Other Calculations

Window SUM is powerful when combined with arithmetic operations.

Difference from Total

-- How much above/below average is each item's price compared to seller total
SELECT 
    seller_id,
    price,
    SUM(price) OVER (PARTITION BY seller_id) AS seller_total,
    price - (SUM(price) OVER (PARTITION BY seller_id) / COUNT(*) OVER (PARTITION BY seller_id)) AS diff_from_avg
FROM olist_order_items_dataset
LIMIT 15;

Revenue Breakdown Analysis

-- Complete revenue breakdown
SELECT 
    seller_id,
    order_id,
    price,
    SUM(price) OVER () AS total_revenue,
    SUM(price) OVER (PARTITION BY seller_id) AS seller_revenue,
    ROUND(100.0 * SUM(price) OVER (PARTITION BY seller_id) / SUM(price) OVER (), 2) AS seller_pct_of_total
FROM olist_order_items_dataset
LIMIT 20;

Practical Use Cases

Use Case 1: Payment Analysis

Analyze payment values with totals by payment type:

-- Payment analysis with percentages
SELECT 
    order_id,
    payment_type,
    payment_value,
    SUM(payment_value) OVER (PARTITION BY payment_type) AS type_total,
    SUM(payment_value) OVER () AS grand_total,
    ROUND(100.0 * payment_value / SUM(payment_value) OVER (PARTITION BY payment_type), 2) AS pct_of_type
FROM olist_order_payments_dataset
LIMIT 20;

Use Case 2: Order Item Analysis

Calculate cumulative order value:

-- Cumulative item value within each order
SELECT 
    order_id,
    order_item_id,
    price,
    SUM(price) OVER (PARTITION BY order_id ORDER BY order_item_id) AS cumulative_order_value,
    SUM(price) OVER (PARTITION BY order_id) AS final_order_total
FROM olist_order_items_dataset
LIMIT 20;

Use Case 3: Revenue Contribution by State

See each customer's contribution to their state's revenue:

-- Revenue contribution analysis by state
SELECT 
    c.customer_state,
    c.customer_city,
    SUM(i.price) AS customer_revenue,
    SUM(SUM(i.price)) OVER (PARTITION BY c.customer_state) AS state_revenue,
    ROUND(100.0 * SUM(i.price) / SUM(SUM(i.price)) OVER (PARTITION BY c.customer_state), 2) AS pct_of_state
FROM olist_customers_dataset c
JOIN olist_orders_dataset o ON c.customer_id = o.customer_id
JOIN olist_order_items_dataset i ON o.order_id = i.order_id
GROUP BY c.customer_state, c.customer_city
ORDER BY c.customer_state, customer_revenue DESC
LIMIT 20;

Practice

Practice using window SUM with these exercises.

Exercise 1: Total and Percentage

Show each payment with the grand total and the payment's percentage of total:

-- Calculate payment percentage of total

SELECT 
    order_id,
    payment_type,
    payment_value,
    SUM(payment_value) OVER () AS grand_total,
    ROUND(100.0 * payment_value / SUM(payment_value) OVER (), 4) AS pct_of_total
FROM olist_order_payments_dataset
LIMIT 20;

Exercise 2: Running Total

Create a running total of item prices ordered by order_id:

-- Running total of prices

SELECT 
    order_id,
    product_id,
    price,
    SUM(price) OVER (ORDER BY order_id) AS running_total
FROM olist_order_items_dataset
LIMIT 20;

Exercise 3: Partition Sum

Show each item with its seller's total revenue and the item's percentage of seller revenue:

-- Seller revenue analysis

SELECT 
    seller_id,
    order_id,
    price,
    SUM(price) OVER (PARTITION BY seller_id) AS seller_total,
    ROUND(100.0 * price / SUM(price) OVER (PARTITION BY seller_id), 2) AS pct_of_seller
FROM olist_order_items_dataset
LIMIT 20;

Key Takeaways

Here's what you've learned about window SUM:

Partition Sum: SUM(col) OVER (PARTITION BY group) for group totals
Grand Total: SUM(col) OVER () for total across all rows
Running Sum: Add ORDER BY for cumulative totals
Percentages: Divide by SUM() OVER () for proportion calculations
Combined Patterns: Mix PARTITION BY and ORDER BY for complex analysis

Common Patterns

Pattern	Purpose
`SUM(col) OVER (PARTITION BY group)`	Group total
`SUM(col) OVER ()`	Grand total
`SUM(col) OVER (ORDER BY sort)`	Running total
`price / SUM(price) OVER ()`	Percentage of total
`SUM(col) OVER (PARTITION BY group ORDER BY sort)`	Running total per group