SUM, AVG, COUNT as window functions in PostgreSQL

Introduction

These functions help you calculate totals, averages, and counts across rows while still keeping each row visible. They let you see both individual and group data together.

When you want to see each sale and also the total sales so far.

When you need the average score of a student along with each test score.

When counting how many orders each customer made without hiding order details.

When comparing each employee's salary to the average salary in their department.

Syntax

PostgreSQL

FUNCTION() OVER ([PARTITION BY column] [ORDER BY column] [ROWS frame])

Replace FUNCTION() with SUM(), AVG(), or COUNT().

PARTITION BY divides rows into groups to calculate separately.

Examples

Calculates total sales over all rows, showing total on each row.

PostgreSQL

SELECT sales, SUM(sales) OVER () AS total_sales FROM orders;

Shows each employee's salary and the average salary in their department.

PostgreSQL

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

Counts how many orders each customer made, shown on every order row.

PostgreSQL

SELECT customer_id, order_id, COUNT(order_id) OVER (PARTITION BY customer_id) AS orders_per_customer FROM orders;

Sample Program

This creates a sales table, adds some sales data, then shows for each sale the running total, average sale amount, and total number of sales per salesperson.

PostgreSQL

CREATE TABLE sales (
  id SERIAL PRIMARY KEY,
  salesperson TEXT,
  amount INT
);

INSERT INTO sales (salesperson, amount) VALUES
('Alice', 100),
('Bob', 200),
('Alice', 150),
('Bob', 50),
('Alice', 200);

SELECT salesperson, amount,
  SUM(amount) OVER (PARTITION BY salesperson ORDER BY id) AS running_total,
  AVG(amount) OVER (PARTITION BY salesperson) AS avg_amount,
  COUNT(*) OVER (PARTITION BY salesperson) AS total_sales
FROM sales
ORDER BY salesperson, id;

Important Notes

Window functions do not reduce the number of rows returned.

ORDER BY inside OVER() can create running totals or moving averages.

PARTITION BY groups rows to calculate separately for each group.

Summary

SUM, AVG, COUNT as window functions calculate values across rows but keep all rows visible.

Use PARTITION BY to group rows for separate calculations.

ORDER BY inside OVER() helps create running or cumulative calculations.