How to Efficiently Create a Cumulative Sum Column in MySQL?

Create cumulative sum column in MySQL

To add a cumulative sum column (cumulative_sum) to the table, you need to update the existing column with a running total. Here are two ways to achieve this in MySQL:

Use correlated subquery:

<code class="language-sql">SELECT
  t.id,
  t.count,
  (
    SELECT
      SUM(x.count)
    FROM table AS x
    WHERE
      x.id <= t.id
  ) AS cumulative_sum
FROM table AS t
ORDER BY
  t.id;</code>

This query uses a nested query to calculate the cumulative sum of each row based on rows with IDs less than or equal to the current row ID.

Use MySQL variables:

<code class="language-sql">SELECT
  t.id,
  t.count,
  (@running_total := @running_total + t.count) AS cumulative_sum
FROM table AS t
JOIN
  (SELECT @running_total := 0) AS r
ORDER BY
  t.id;</code>

This method utilizes MySQL variables to track the running total while iterating over rows. The @running_total variable is initialized to 0 and then incremented for each row.

Note:

• These methods are specific to MySQL and may not be portable to other databases.
• Maintaining the correct order is crucial, especially when using variables for complex calculations.

The above is the detailed content of How to Efficiently Create a Cumulative Sum Column in MySQL?. For more information, please follow other related articles on the PHP Chinese website!