Window Function SUM in SQL

Level 5180 keystrokes

For each 2024 order, SUM(total) OVER (ORDER BY order_date) gives a running total (150, 350, 650), while SUM(total) OVER () with an empty window gives the grand total 650.00 on every row.

SQL code

-- Returns each 2024 order with a running total and the grand total
1SELECT
2 order_date,
3 total,
-- A window SUM keeps every row (unlike GROUP BY) and adds a sum to each
4 SUM(total) OVER (
-- With ORDER BY, the sum covers rows from the first up to the current date: 150, 350, 650
-- Rows with the same order_date are summed together (default RANGE frame)
5 ORDER BY order_date
6 ) AS running_total,
-- Empty OVER (): the window is all rows, so every row shows 650.00
7 SUM(total) OVER () AS grand_total
8FROM
-- Reads from the orders table
9 orders
-- WHERE runs before the window sums, so only 2024 orders are added up
10WHERE
11 YEAR(order_date) = 2024
-- Shows the rows in date order
12ORDER BY
13 order_date;

Output

+------------+--------+---------------+-------------+
| order_date | total  | running_total | grand_total |
+------------+--------+---------------+-------------+
| 2024-01-15 | 150.00 |        150.00 |      650.00 |
| 2024-02-20 | 200.00 |        350.00 |      650.00 |
| 2024-03-10 | 300.00 |        650.00 |      650.00 |
+------------+--------+---------------+-------------+
3 rows in set
Practice typing this code

Practice typing this code. Results are not recorded in the rankings.

Symbols in this code

The symbols you type in this SQL code and how often they appear.

  • _× 6
  • (× 5
  • )× 5
  • ,× 3
  • ;× 1
  • =× 1

More SQL examples at level 5

← SQL typing practice