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 total1SELECT2 order_date,3 total, -- A window SUM keeps every row (unlike GROUP BY) and adds a sum to each4 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_date6 ) AS running_total, -- Empty OVER (): the window is all rows, so every row shows 650.007 SUM(total) OVER () AS grand_total8FROM -- Reads from the orders table9 orders-- WHERE runs before the window sums, so only 2024 orders are added up10WHERE11 YEAR(order_date) = 2024-- Shows the rows in date order12ORDER BY13 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. 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