SUM/COUNT/AVG OVER

Concepts covered: Aggregate Window Fns

Sections 2 and 3 used ORDER BY inside OVER with functions that produce positions. Put it on an aggregate and the aggregate changes shape. SUM(salary) OVER (ORDER BY hire_date) no longer returns 1 total for the window; for each row it sums the rows that sort at or before that row, so the result grows as the order advances. That is a running total. The rule underneath is that an ordered window has a default frame, the rows from the start of the window up to the current one, and the Intermediate lesson covers changing it; this section uses the default throughout.

In hire order the payroll grew like this. Dave was hired first, in February 2019, and the payroll at that point was his 80,000. Alice joined in September 2019 and the running figure became 200,000. Frank brought it to 295,000 in April 2020, Eve to 385,000 in November 2021, Carol to 515,000 in June 2022 and Grace to 605,000 in March 2023. The last row's running total equals the SUM(salary) OVER () from section 0, and that holds for every running total: it ends where the grand total is.

The animation sums the rows in hire order; the frame widens by 1 row at each step and the total is written onto the row that closed the frame.

SELECT
  emp_name,
  hire_date,
  salary,
  SUM(salary) OVER (ORDER BY hire_date) AS payroll_so_far
FROM employees

COUNT and AVG along the same order

The same ORDER BY turns COUNT into a running headcount and AVG into a running average. COUNT(*) OVER (ORDER BY hire_date) returns 1, 2, 3, 4, 5, 6: the headcount after each hire. AVG(salary) OVER (ORDER BY hire_date) returns 80,000, then 100,000, 98,333, 96,250, 103,000 and 100,833: the average pay of the workforce as it stood after each hire, and each value is the running total divided by the running count on the same row. All 3 use the identical frame, and all 3 come from 1 pass over the 6 rows.

SELECT emp_name, hire_date, salary,
       SUM(salary) OVER (ORDER BY hire_date) AS payroll_so_far,
       COUNT(*) OVER (ORDER BY hire_date) AS headcount,
       ROUND(AVG(salary) OVER (ORDER BY hire_date), 0) AS avg_so_far
FROM employees
ORDER BY hire_date

Swap hire_date for salary in all 3 windows and the running total becomes cumulative payroll from the lowest earner upward, the column behind a chart that asks what fraction of payroll the lowest-paid half of staff receives. The order column is the axis the running figure moves along, and a date is only the most common choice.

Ties in the order column

The default frame is defined by value, and rows with equal order values are peers: each one's frame includes all of its peers. With 2 people hired on the same day, both rows show the running total after both hires, and the figure appears to jump by 2 salaries at once. That is right for a question about payroll on a given date and wrong for a question about payroll after each individual hire. A tiebreaker in the ORDER BY (ORDER BY hire_date, employee_id) makes every row its own step, the same remedy section 2 applied to ROW_NUMBER. Dates in the running example are all distinct, so the 6-row table never shows the effect; a production table with 400 hires in a year will.

Running totals inside partitions

PARTITION BY and ORDER BY combine in 1 OVER. SUM(salary) OVER (PARTITION BY department ORDER BY hire_date) restarts the running total in each department: Sales goes 80,000 then 170,000, Engineering 120,000 then 250,000, Marketing 95,000 then 185,000. Each partition's last row carries the department total from section 1, and the order inside 1 partition has no effect on any other. This is the shape of month-to-date revenue per store, cumulative spend per customer and a running error count per service.

The blanks below ask for that shape on the practice employees table: the aggregate, and the order the total accumulates in.

Every distractor runs. ORDER BY salary gives a cumulative payroll ordered by pay within each department, ORDER BY emp_name an alphabetical one, and COUNT a running headcount; the query never fails, so the check is reading 1 department's rows and confirming the column climbs in hire order and ends on the department total.

Do

• Name the grain first: say what 1 row is before adding a window, because the row count will not change.
• End every ORDER BY inside OVER with a unique column when the result must be reproducible.
• Match the function to the question: ROW_NUMBER for fixed counts, RANK for places, DENSE_RANK for nth value.
• Filter on a window result from an outer query, never from the same query's WHERE.

Avoid

• Do not self-join to put a group total beside a detail row; OVER (PARTITION BY) does it in 1 pass.
• Do not read repeated values in a window column as duplicates; each is computed for its own row's window.
• Do not expect ROW_NUMBER to be stable across ties without a tiebreaker column.
• Do not assume an ordered SUM steps 1 row at a time when the order column has ties.

About this lesson

This section is part of the Window Functions: Beginner lesson on DataDriven, the home of data engineering. Lessons cover SQL, Python, Spark, data modeling and pipeline architecture, and each section pairs its explanation with worked examples and practice you run as you go.