Answer

The aggregate function SUM() calculates the sum of values across an entire group of rows in the result set, returning a single value for each group (or the entire query). The window function SUM() allows you to compute the sum over a "window" of values defined within the SELECT (using OVER()), while each row is retained in the result and can access the aggregated result of its own "window". This is especially useful for obtaining interim totals for groups.

Example of aggregate function:

SELECT department_id, SUM(salary)
FROM employees
GROUP BY department_id;

Example of window function:

SELECT department_id, salary,
       SUM(salary) OVER (PARTITION BY department_id ORDER BY hire_date)
FROM employees;

Trick Question

“Can you get a cumulative (running) sum for a column using only the aggregate function GROUP BY?”

Answer: No! GROUP BY only provides the final aggregates; a running sum requires either a window function or complex subqueries that are more complicated and slower.

Example:

SELECT salary,
  SUM(salary) OVER (ORDER BY hire_date) AS running_total
FROM employees;

History

In the transaction report, the running sum was implemented with multiple correlated subqueries. As the table grew, performance dropped dramatically. Switching to a window function returned the performance to seconds.

History

Attempting to create complex analytics with group totals using GROUP BY resulted in several stages of extraction and processing in external software. Window functions enabled generating the required report in a single SQL query.

History

An analyst wrote a query with window functions, using the wrong PARTITION BY — results for different groups got mixed up. The result was distorted reporting. After correcting the grouping breakdown, the data became accurate.

What is the difference between the aggregate function SUM() and the window function SUM()? In what tasks is it preferable to use window functions? Provide an example.

Answer

Trick Question