SUM, AVG, COUNT as window functions in PostgreSQL - Step-by-Step Execution

Concept Flow - SUM, AVG, COUNT as window functions

Start with table rows

↓

Apply window function

↓

Partition rows if specified

↓

Order rows if specified

↓

Calculate running SUM, AVG, COUNT

↓

Add results as new columns

↓

Return result set with window function values

Window functions compute values across sets of rows related to the current row, adding results as new columns without collapsing rows.

Execution Sample

PostgreSQL

SELECT dept, emp, salary,
       SUM(salary) OVER (PARTITION BY dept) AS dept_sum,
       AVG(salary) OVER (PARTITION BY dept) AS dept_avg,
       COUNT(*) OVER (PARTITION BY dept) AS dept_count
FROM employees;

Calculates sum, average, and count of salaries per department, showing these as new columns for each employee.

Execution Table

Step	Row (dept, emp, salary)	SUM(salary) OVER (PARTITION BY dept)	AVG(salary) OVER (PARTITION BY dept)	COUNT(*) OVER (PARTITION BY dept)
1	(Sales, Alice, 5000)	15000	5000	3
2	(Sales, Bob, 7000)	15000	5000	3
3	(Sales, Carol, 3000)	15000	5000	3
4	(HR, Dave, 4000)	9000	4500	2
5	(HR, Eve, 5000)	9000	4500	2
6	(IT, Frank, 6000)	6000	6000	1
Exit	All rows processed	-	-	-

💡 All rows processed, window functions computed per partition

Variable Tracker

Variable	Start	After Row 1	After Row 2	After Row 3	After Row 4	After Row 5	After Row 6	Final
dept_sum (Sales)	0	15000	15000	15000	15000	15000	15000	15000
dept_avg (Sales)	0	5000	5000	5000	5000	5000	5000	5000
dept_count (Sales)	0	3	3	3	3	3	3	3
dept_sum (HR)	0	0	0	0	9000	9000	9000	9000
dept_avg (HR)	0	0	0	0	4500	4500	4500	4500
dept_count (HR)	0	0	0	0	2	2	2	2
dept_sum (IT)	0	0	0	0	0	0	6000	6000
dept_avg (IT)	0	0	0	0	0	0	6000	6000
dept_count (IT)	0	0	0	0	0	0	1	1

Key Moments - 3 Insights

Why does the SUM value repeat for each row in the same department?

Because window functions calculate over the partition but do not collapse rows, so each row in the partition shows the same SUM value (see execution_table rows 1-3).

How is AVG calculated without grouping rows?

AVG is computed as SUM divided by COUNT over the partition for each row, shown as a new column without changing the number of rows (see execution_table AVG column).

Why does COUNT(*) show the total number of rows in the partition for every row?

COUNT(*) counts all rows in the partition and repeats this count for each row, unlike aggregate functions that collapse rows (see execution_table COUNT column).

Visual Quiz - 3 Questions

Look at the execution_table, what is the SUM(salary) for Bob in Sales at step 2?

A 15000

B 7000

C 5000

D 3000

💡 Hint

Check the SUM(salary) column for step 2 in the execution_table.

At which step does the COUNT(*) for HR become 2?

A Step 3

B Step 4

C Step 5

D Step 6

💡 Hint

Look at the COUNT(*) column and find when HR rows start showing 2.

If a new employee with salary 4000 is added to IT, how would the AVG for IT change in variable_tracker?

A It would stay 6000

B It would become 4000

C It would become 5000

D It would become 10000

💡 Hint

AVG is total salary divided by count; adding 4000 to 6000 total over 2 rows changes average.

Concept Snapshot

SUM, AVG, COUNT as window functions:
- Use OVER() clause to apply without grouping
- PARTITION BY divides rows into groups
- Functions compute values per partition, repeated per row
- Rows stay same count, new columns added
- Useful for running totals, averages, counts per group

Full Transcript

This lesson shows how SUM, AVG, and COUNT work as window functions in PostgreSQL. Window functions calculate values across a set of rows related to the current row, defined by PARTITION BY. Unlike aggregate functions, they do not reduce the number of rows but add new columns with the computed results. For example, SUM(salary) OVER (PARTITION BY dept) calculates the total salary per department and shows it on every row in that department. AVG and COUNT work similarly, computing average salary and number of employees per department. The execution table traces each row's values step-by-step, showing how the window functions produce repeated results per partition. Key moments clarify why values repeat and how averages are computed without grouping. The quiz tests understanding of these repeated values and how changes affect averages. This helps beginners see how window functions add powerful analytics without losing row detail.

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