Скачать или смотреть How to Create a Cumulative Sum Grouped by Column in MySQL

Learn how to efficiently calculate cumulative sums for multiple state columns in MySQL with a step-by-step guide.
---
This video is based on the question https://stackoverflow.com/q/64655203/ asked by the user 'dynsne' ( https://stackoverflow.com/u/2671017/ ) and on the answer https://stackoverflow.com/a/64655383/ provided by the user 'Gordon Linoff' ( https://stackoverflow.com/u/1144035/ ) at 'Stack Overflow' website. Thanks to these great users and Stackexchange community for their contributions.

Visit these links for original content and any more details, such as alternate solutions, latest updates/developments on topic, comments, revision history etc. For example, the original title of the Question was: How to create a cumulative sum grouped by column

Also, Content (except music) licensed under CC BY-SA https://meta.stackexchange.com/help/l...
The original Question post is licensed under the 'CC BY-SA 4.0' ( https://creativecommons.org/licenses/... ) license, and the original Answer post is licensed under the 'CC BY-SA 4.0' ( https://creativecommons.org/licenses/... ) license.

If anything seems off to you, please feel free to write me at vlogize [AT] gmail [DOT] com.
---
How to Create a Cumulative Sum Grouped by Column in MySQL

If you're managing a database with time series data, such as states over time in a MySQL table, you might find yourself needing to calculate cumulative sums grouped by certain columns. In this guide, we'll explore how to achieve this using a specific example involving states tracked over time.

The Problem Statement

Imagine you have a database table that logs different states of interest over time. Here’s a simplified version of how your table might look:

updated_atstate_onestate_two12/31/1999121/1/2000231/2/200003You need to create a SQL query to calculate the cumulative sums for each row filtered by the specific states in both state_one and state_two. The desired output format would include two additional columns showing these cumulative sums.

A Simple SQL Query

When tackling this requirement, the key is to understand how to properly write your SQL query. Instead of getting entangled in complicated logic, you can utilize correlated subqueries for a straightforward solution. Below is the query you can use:

[[See Video to Reveal this Text or Code Snippet]]

Explanation of the Query:

FROM Clause: You begin with selecting all columns from your primary table table_of_interest.

Correlated Subqueries:

The first subquery counts how many times state 1 has occurred up to the current updated_at date for state_one.

The second subquery does the same for state 2, but for the state_two column.

Counting Logic: Each subquery uses a condition that checks if the updated_at date in the subquery table (t2) is before or equal to the current row’s updated_at date in the outer query (t), thereby ensuring an accurate cumulative total.

Result:

Using the above query would yield results similar to this:

updated_atstate_onestate_twocumulative_sum_one_1cumulative_sum_two_212/31/199912111/1/200023121/2/20000112Conclusion

Calculating cumulative sums in SQL, specifically in MySQL, doesn’t have to be a daunting task. By leveraging correlated subqueries, you can get precise cumulative values for each state and refine your data analysis capabilities efficiently.

If you have more complex conditions or if your business logic requires different states, you can adjust the WHERE clause in the subqueries accordingly. Happy querying!