SUM, AVG, COUNT as window functions in PostgreSQL - Deep Dive

Overview - SUM, AVG, COUNT as window functions

What is it?

SUM, AVG, and COUNT are functions that calculate totals, averages, and counts of rows. When used as window functions, they perform these calculations across a set of rows related to the current row without collapsing the result into a single summary row. This means you can see the original data alongside running totals, averages, or counts that update as you move through the data.

Why it matters

Without window functions, you would have to write complex queries or multiple steps to get running totals or averages alongside each row. This makes analysis slower and harder to understand. Window functions let you quickly see trends and summaries in your data while keeping all the details visible, which is crucial for reports, dashboards, and data exploration.

Where it fits

Before learning window functions, you should understand basic SQL aggregation like SUM, AVG, and COUNT with GROUP BY. After mastering window functions, you can explore more advanced window features like framing, ranking functions, and performance tuning.

Mental Model

Core Idea

Window functions calculate aggregates over a moving set of rows related to each row, keeping all rows visible.

Think of it like...

Imagine you are reading a book and keeping a running total of pages read so far on each page. You don’t close the book or summarize it; you just note the total pages read up to that point on every page.

┌───────────────┐
│ Row 1 │ Value=10 │ Running SUM=10 │
├───────────────┤
│ Row 2 │ Value=20 │ Running SUM=30 │
├───────────────┤
│ Row 3 │ Value=15 │ Running SUM=45 │
└───────────────┘

Build-Up - 7 Steps

1

Foundation

Basic aggregation functions explained

Concept: Learn what SUM, AVG, and COUNT do in simple SQL queries.

SUM adds up all values in a column. AVG finds the average value. COUNT counts how many rows or non-null values exist. For example, SELECT SUM(sales) FROM orders; returns the total sales.

Result

You get a single number representing the total, average, or count for the whole table or group.

Understanding these basic functions is essential because window functions build on them to provide more detailed insights.

2

Foundation

GROUP BY limits aggregation scope

Concept: GROUP BY groups rows so aggregation functions summarize each group separately.

If you want totals per category, you use GROUP BY category. For example, SELECT category, SUM(sales) FROM orders GROUP BY category; returns total sales per category.

Result

You get one row per group with the aggregated value.

GROUP BY collapses rows into summary rows, which means you lose the original row details.

3

Intermediate

Window functions keep all rows visible

🤔Before reading on: do you think window functions remove rows like GROUP BY or keep all rows? Commit to your answer.

Concept: Window functions calculate aggregates over a set of rows related to the current row but keep all rows in the output.

Using SUM() OVER() computes a running total or total over all rows without grouping. For example, SELECT id, sales, SUM(sales) OVER() FROM orders; shows each sale and the total sales repeated on every row.

Result

You see the original rows plus the aggregate value repeated or calculated per row.

Knowing that window functions don’t collapse rows helps you combine detailed data with summary insights in one query.

4

Intermediate

Partitioning data with window functions

🤔Before reading on: do you think window functions can calculate aggregates separately for groups? Commit to yes or no.

Concept: You can divide rows into partitions (groups) so the window function calculates aggregates within each partition.

Using PARTITION BY inside OVER() limits the aggregate to each group. For example, SELECT category, sales, SUM(sales) OVER(PARTITION BY category) FROM orders; shows total sales per category repeated on each row of that category.

Result

Each row shows the aggregate for its group, not the whole table.

Partitioning lets you get group-level aggregates without losing row details, combining the best of GROUP BY and window functions.

5

Intermediate

Using frame clauses to control window range

🤔Before reading on: do you think window functions always consider all rows in a partition? Commit to yes or no.

Concept: Frames define which rows around the current row are included in the aggregate calculation.

You can specify ROWS BETWEEN or RANGE BETWEEN to limit the window. For example, SUM(sales) OVER(ORDER BY date ROWS BETWEEN UNBOUNDED PRECEDING AND CURRENT ROW) calculates a running total up to the current row.

Result

Aggregates can be running totals, moving averages, or other partial summaries.

Frames give precise control over how aggregates move through data, enabling complex time-series and trend analyses.

6

Advanced

COUNT as a window function nuances

🤔Before reading on: does COUNT() as a window function count all rows or only non-null values? Commit to your answer.

Concept: COUNT() counts non-null values in the window frame, which can differ from COUNT(*) that counts all rows.

For example, COUNT(column) OVER(PARTITION BY category) counts non-null values of column per category, while COUNT(*) OVER() counts all rows in the window.

Result

You get precise counts that can exclude nulls, useful for data quality checks.

Understanding COUNT’s behavior prevents mistakes when nulls exist and accurate counts are needed.

7

Expert

Performance and optimization of window functions

🤔Before reading on: do you think window functions always perform slower than GROUP BY? Commit to yes or no.

Concept: Window functions can be optimized by the database engine but may be costly on large datasets without indexes or proper partitioning.

PostgreSQL uses sorting and indexing to speed up window functions. Using PARTITION BY and ORDER BY wisely reduces computation. Avoid unnecessary large frames. EXPLAIN ANALYZE helps find bottlenecks.

Result

Efficient queries that use window functions can run fast even on big data.

Knowing how window functions execute helps write queries that balance detail and speed in production.

Under the Hood

Window functions operate by scanning the dataset and, for each row, defining a window frame of rows based on partitioning and ordering rules. The aggregate function then computes its result over this frame without collapsing rows. Internally, the database engine sorts and buffers rows to efficiently calculate these aggregates for each row.

Why designed this way?

Window functions were designed to provide detailed row-level insights alongside aggregates without losing data granularity. Traditional GROUP BY aggregates lose row details, so window functions fill this gap. The design balances expressiveness and performance by allowing flexible partitioning and framing.

┌───────────────┐
│ Input Rows    │
├───────────────┤
│ Partitioning  │
│ (GROUPS)      │
├───────────────┤
│ Ordering      │
│ (SORTING)     │
├───────────────┤
│ Window Frame  │
│ (ROWS RANGE)  │
├───────────────┤
│ Aggregate    │
│ Calculation  │
├───────────────┤
│ Output Rows   │
│ (Original +  │
│ Aggregate)   │
└───────────────┘

Myth Busters - 4 Common Misconceptions

Quick: Does SUM() OVER() collapse rows like GROUP BY? Commit to yes or no.

Common Belief: SUM() OVER() works just like GROUP BY and reduces rows to one per group.

Reality: SUM() OVER() keeps all original rows and adds the sum as a new column without collapsing rows.

Why it matters: Misunderstanding this leads to wrong query designs that lose data detail or produce unexpected results.

Quick: Does COUNT() OVER() count null values? Commit to yes or no.

Common Belief: COUNT() OVER() counts all rows including nulls in the counted column.

Reality: COUNT(column) OVER() counts only non-null values in that column; COUNT(*) OVER() counts all rows.

Why it matters: Confusing these causes incorrect counts, especially when data has nulls, leading to wrong analysis.

Quick: Do window functions always perform slower than GROUP BY? Commit to yes or no.

Common Belief: Window functions are always slower and less efficient than GROUP BY aggregates.

Reality: Window functions can be optimized and sometimes perform comparably or better, especially when detailed row data is needed.

Why it matters: Assuming window functions are too slow may prevent using powerful, concise queries that improve maintainability.

Quick: Does PARTITION BY in window functions filter rows? Commit to yes or no.

Common Belief: PARTITION BY filters rows to only those in the partition.

Reality: PARTITION BY divides rows into groups for aggregation but does not filter out any rows from the result.

Why it matters: Misunderstanding this leads to incorrect assumptions about which rows appear in the output.

Expert Zone

1

Window functions can be combined with different frame clauses to create complex moving aggregates like moving averages or cumulative sums.

2

The order of rows in the window frame affects the result; careful use of ORDER BY inside OVER() is crucial for correct calculations.

3

Using window functions with large partitions and wide frames can cause high memory usage; understanding execution plans helps optimize queries.

When NOT to use

Avoid window functions when you only need simple group summaries and want minimal output rows; GROUP BY is simpler and often faster. For very large datasets where performance is critical, consider pre-aggregating data or using materialized views.

Production Patterns

Common patterns include running totals for financial reports, moving averages for trend analysis, and counts per category alongside detailed rows in dashboards. Experts often combine window functions with CTEs and indexes for efficient, readable queries.

Connections

Streaming data processing

Both use moving windows to calculate aggregates over data streams or tables.

Understanding window functions helps grasp how streaming systems compute real-time aggregates over sliding windows.

Time series analysis

Window functions enable running totals and moving averages, key tools in time series data analysis.

Knowing window functions deepens understanding of how to analyze trends and seasonality in time-based data.

Functional programming reduce operations

Window functions resemble reduce operations applied over subsets of data with context.

Recognizing this connection clarifies how aggregation with context works across different programming paradigms.

Common Pitfalls

#1 Using window functions without ORDER BY when order matters

Wrong approach:

SELECT id, sales, SUM(sales) OVER() FROM orders;

Correct approach:

SELECT id, sales, SUM(sales) OVER(ORDER BY id) FROM orders;

Root cause:

Not specifying ORDER BY means the window frame is unordered, so running totals or cumulative aggregates may be incorrect or meaningless.

#2 Confusing COUNT(column) with COUNT(*) in window functions

Wrong approach:

SELECT id, COUNT(column) OVER() FROM table;

Correct approach:

SELECT id, COUNT(*) OVER() FROM table;

Root cause:

COUNT(column) excludes nulls, so counts differ if nulls exist; misunderstanding this leads to wrong counts.

#3 Using PARTITION BY expecting it to filter rows

Wrong approach:

SELECT * FROM orders WHERE PARTITION BY category;

Correct approach:

SELECT *, SUM(sales) OVER(PARTITION BY category) FROM orders;

Root cause:

PARTITION BY is part of window function syntax, not a filtering clause; misunderstanding causes syntax errors or wrong queries.

Key Takeaways

SUM, AVG, and COUNT as window functions let you calculate aggregates across related rows without losing individual row details.

Window functions keep all rows visible and add aggregate results as new columns, unlike GROUP BY which collapses rows.

Partitioning and framing control which rows are included in each aggregate calculation, enabling flexible analyses like running totals and moving averages.

Understanding the difference between COUNT(*) and COUNT(column) is crucial to avoid counting errors with null values.

Proper use of ORDER BY and frame clauses inside window functions ensures accurate and meaningful aggregate results.

Practice

1. What does the COUNT(*) OVER() window function do in PostgreSQL?

easy

A. Counts all rows in the result set without grouping them.

B. Counts only distinct rows in the table.

C. Counts rows and groups them by a specified column.

D. Counts rows but returns only one aggregated result.

Solution

1. Step 1: Understand COUNT(*) OVER() usage

   The function counts all rows in the query result but does not group or collapse rows.

2. Step 2: Difference from GROUP BY

   Unlike GROUP BY, it keeps all rows visible and adds the count as a column.

3. Final Answer:

   Counts all rows in the result set without grouping them. -> Option A

4. Quick Check:

   COUNT(*) OVER() counts all rows [OK]

Hint: Window COUNT(*) counts all rows without grouping [OK]

Common Mistakes:

• Confusing window COUNT with GROUP BY count
• Thinking it returns a single aggregated row
• Assuming it counts distinct values only

2. Which of the following is the correct syntax to calculate a running total of sales using SUM as a window function in PostgreSQL?

easy

A. SELECT sales, SUM(sales) OVER (ORDER BY date) AS running_total FROM sales_data;

B. SELECT sales, SUM(sales) FROM sales_data ORDER BY date;

C. SELECT sales, SUM(sales) PARTITION BY date FROM sales_data;

D. SELECT sales, SUM(sales) OVER PARTITION BY date FROM sales_data;

Solution

1. Step 1: Identify correct window function syntax

   SUM(sales) OVER (ORDER BY date) calculates cumulative sum ordered by date.

2. Step 2: Check other options for syntax errors

   The other options misuse window function syntax or omit OVER keyword.

3. Final Answer:

   SELECT sales, SUM(sales) OVER (ORDER BY date) AS running_total FROM sales_data; -> Option A

4. Quick Check:

   SUM() OVER (ORDER BY ...) is correct syntax [OK]

Hint: Use OVER() with ORDER BY for running totals [OK]

Common Mistakes:

• Omitting OVER() clause
• Using PARTITION BY without OVER()
• Confusing GROUP BY with window functions

3. Given the table employees with columns department and salary, what will the following query return?

SELECT department, salary, AVG(salary) OVER (PARTITION BY department) AS avg_dept_salary FROM employees;

medium

A. Syntax error due to missing GROUP BY clause.

B. Each row shows salary and overall average salary of all employees.

C. Each row shows salary and average salary of all departments combined.

D. Each row shows salary and average salary of its department.

Solution

1. Step 1: Understand PARTITION BY in AVG window function

   AVG(salary) OVER (PARTITION BY department) calculates average salary per department for each row.

2. Step 2: Confirm output per row

   Each row keeps salary and adds average salary of that row's department without grouping rows.

3. Final Answer:

   Each row shows salary and average salary of its department. -> Option D

4. Quick Check:

   AVG() OVER (PARTITION BY ...) averages per group per row [OK]

Hint: PARTITION BY groups rows but keeps all rows visible [OK]

Common Mistakes:

• Thinking it aggregates to one row per group
• Confusing window AVG with GROUP BY AVG
• Expecting overall average without PARTITION BY

4. Identify the error in this query:

SELECT employee_id, salary, COUNT(*) OVER PARTITION BY department FROM employees;

medium

A. COUNT(*) cannot be used as a window function.

B. Missing parentheses around PARTITION BY clause in OVER().

C. employee_id cannot be selected with window functions.

D. No error; query is correct.

Solution

1. Step 1: Check syntax of window function

   OVER clause must have parentheses: OVER (PARTITION BY department).

2. Step 2: Validate other parts

   COUNT(*) is valid as window function; selecting employee_id is allowed.

3. Final Answer:

   Missing parentheses around PARTITION BY clause in OVER(). -> Option B

4. Quick Check:

   OVER() requires parentheses [OK]

Hint: Always use parentheses after OVER keyword [OK]

Common Mistakes:

• Omitting parentheses in OVER()
• Thinking COUNT(*) can't be window function
• Assuming window functions require GROUP BY

5. You want to find the cumulative count of orders per customer ordered by order date. Which query correctly uses COUNT as a window function to achieve this?

hard

A. SELECT customer_id, order_date, COUNT(*) FROM orders GROUP BY customer_id, order_date ORDER BY order_date;

B. SELECT customer_id, order_date, COUNT(*) OVER (ORDER BY order_date) AS cumulative_orders FROM orders;

C. SELECT customer_id, order_date, COUNT(*) OVER (PARTITION BY customer_id ORDER BY order_date) AS cumulative_orders FROM orders;

D. SELECT customer_id, order_date, COUNT(*) OVER (PARTITION BY order_date ORDER BY customer_id) AS cumulative_orders FROM orders;

Solution

1. Step 1: Understand cumulative count per customer

   COUNT(*) OVER (PARTITION BY customer_id ORDER BY order_date) counts rows per customer in order of order_date cumulatively.

2. Step 2: Check other options for correctness

   SELECT customer_id, order_date, COUNT(*) FROM orders GROUP BY customer_id, order_date ORDER BY order_date; uses GROUP BY, not cumulative count; C lacks partition by customer; D partitions by order_date, not customer.

3. Final Answer:

   SELECT customer_id, order_date, COUNT(*) OVER (PARTITION BY customer_id ORDER BY order_date) AS cumulative_orders FROM orders; -> Option C

4. Quick Check:

   Use PARTITION BY customer_id with ORDER BY for cumulative count [OK]

Hint: Partition by customer and order by date for cumulative count [OK]

Common Mistakes:

• Missing PARTITION BY customer_id
• Using GROUP BY instead of window function
• Partitioning by wrong column