Running totals are an important concept in SQL because they allow you to compute cumulative sums of values in your data. There are, however, two sorts of running totals to consider: unpartitioned and partitioned. In this post, we will look at these two types, their applications, and examples to help you understand their importance in data analysis.
Running Totals Without Partitions
Without any resets or specified criteria, unpartitioned running totals are generated throughout the full dataset.
They are useful when tracking cumulative values across all data sets, such as when computing cumulative sales over time.
Example
Assume we have the following data in a table called Orders.
--Create Table
CREATE TABLE Orders (
Order_Date DATE,
Customer_Id INT,
Sales INT
);
-- Insert some data into the table
INSERT INTO Orders (Order_Date, Customer_Id, Sales) VALUES
('2023-01-01', 1, 100),
('2023-01-02', 1, 50),
('2023-01-03', 2, 200),
('2023-01-04', 2, 100),
('2023-01-05', 1, 100),
('2023-01-06', 2, 100);
To calculate an Unpartitioned running total of sales, you can use the following SQL query.
SELECT Customer_Id,Order_Date,Sales,
SUM(Sales) OVER (ORDER BY Order_Date) AS Running_Total
FROM Orders Order By Order_Date;
Output
The result accumulates the running total across all dates without any resets.
Partitioned Running Totals
Partitioned running totals are calculated over a subset of the data based on specific criteria or partitions.
They are useful when you want to calculate totals within specific categories or groups, like counting daily sales separately or calculating totals for different customer segments.
Example
Suppose we have the same table called Orders as above in the unpartitioned example.
To calculate a Partitioned running total of sales within each customer_id, you can use the following SQL query.
SELECT Customer_Id,Order_Date,Sales,
SUM(Sales) OVER (PARTITION BY Customer_Id ORDER BY Order_Date) AS Running_Total
FROM Orders Order By Customer_Id,Order_Date
The result accumulates the running total separately for each customer_id.
Summary
In this article, we learn about how to calculate running totals in SQL. Running totals are powerful tools for cumulative calculations. Understanding the difference between unpartitioned and partitioned running totals is essential for effective data analysis.
Unpartitioned running totals accumulate values across the entire dataset, while Partitioned running totals allow you to calculate totals within specific partitions, making them invaluable for segmenting and analyzing data. Incorporate these concepts into your SQL projects to enhance your data analysis capabilities.
If you find this article valuable, please consider liking it and sharing your thoughts in the comments.
Thank you, and happy coding!