Why SUM, AVG, COUNT as window functions in PostgreSQL? - Purpose & Use Cases

The Big Idea

What if your database could do all the running totals and averages for you, instantly and perfectly?

The Scenario

Imagine you have a big list of sales data for a store, and you want to know the running total of sales, the average sale amount so far, and how many sales have happened up to each point in time.

Doing this by hand means writing many separate queries or copying numbers into a spreadsheet and calculating step-by-step.

The Problem

Manually adding up numbers or calculating averages for each row is slow and easy to mess up.

It's hard to keep track of running totals or counts without missing something or making mistakes.

Also, if the data changes, you have to redo all the work again.

The Solution

Using SUM, AVG, and COUNT as window functions lets you calculate running totals, averages, and counts directly in your query.

This means the database does the hard work for you, updating results automatically for each row without extra queries.

Before vs After

✗ Before

SELECT date, sale_amount FROM sales;
-- Then manually add running totals in a spreadsheet

✓ After

SELECT date, sale_amount,
       SUM(sale_amount) OVER (ORDER BY date ROWS UNBOUNDED PRECEDING) AS running_total,
       AVG(sale_amount) OVER (ORDER BY date ROWS UNBOUNDED PRECEDING) AS running_avg,
       COUNT(*) OVER (ORDER BY date ROWS UNBOUNDED PRECEDING) AS running_count
FROM sales;

What It Enables

You can quickly see running totals, averages, and counts for each row, making data analysis faster and more accurate.

Real Life Example

A store manager can instantly see how daily sales add up over the month, the average sale amount so far, and how many sales have been made, all in one report.

Key Takeaways

Manual calculations are slow and error-prone.

Window functions calculate running totals, averages, and counts automatically.

This makes data analysis easier, faster, and more reliable.