Create a mysql cumulative sum column from generated column

Question

I've got the following query

SELECT date, t1.col1, t2.col2, t1.col1 * t2.col2 AS `col3`
FROM t1
INNER JOIN t2 ON t2.col3 = t1.col3
WHERE t2.col3 = 2
ORDER BY date ASC

Now I want to add a column that is a cumulative sum of col3, so following this question (Create a Cumulative Sum Column in MySQL) I came up with this:

SET @csum := 0;
SELECT date, t1.col1, t2.col2, t1.col1 * t2.col2 AS `col3`, (@csum := @csum + `col3`) AS `sum`
FROM t1
INNER JOIN t2 ON t2.col3 = t1.col3
WHERE t2.col3 = 2
ORDER BY date ASC

But as col3 is a generated in the query as well, it obviously doesn't work. How can I change it so the generated column can have a cumulative sum?

score 2 · Accepted Answer · answered Jan 12 '17 at 11:26

2

You can either use the same formula as for col3 also in the @csum-expression, or you could use a surrounding select statement adding the sum-column.

So either...

SET @csum := 0;
SELECT date, t1.col1, t2.col2, t1.col1 * t2.col2 AS `col3`, (@csum := @csum + (t1.col1 * t2.col2)) AS `sum`
FROM t1
INNER JOIN t2 ON t2.col3 = t1.col3
WHERE t2.col3 = 2
ORDER BY date ASC

or ...

SET @csum := 0;
SELECT ti.*, (@csum := @csum + ti.col3) AS `sum`
FROM (
  SELECT date, t1.col1, t2.col2, t1.col1 * t2.col2 AS `col3`, 
  FROM t1
  INNER JOIN t2 ON t2.col3 = t1.col3
  WHERE t2.col3 = 2
  ORDER BY date ASC) ti

answered Jan 12 '17 at 11:26

Stephan Lechner

34,891
4
35
58

Hmm this is working in phpmyadmin, but when running it on the php page, it doesn't work. Any ideas? – CalvT Jan 12 '17 at 11:51
Without knowing the php-code, I suppose that you have to do two queries, one for `SET @csum := 0`, and then for the actual query (cf. also SO answer http://stackoverflow.com/a/20484963/2630032). If this does not help, please post the relevant portion of your php-code, probably as a new question. – Stephan Lechner Jan 12 '17 at 14:26
Stephan - please see my answer below, I'm leaving yours as the accepted one, as I'm pretty sure your query is better than mine :) – CalvT Jan 12 '17 at 17:13

score 0 · Answer 2 · answered Jan 12 '17 at 11:39

0

SET @csum := 0;

SELECT
    date, t1.col1, t2.col2,
    @tmp := t1.col1 * t2.col2 AS `col3`,
    @csum := @csum + @tmp AS `sum`
FROM t1
INNER JOIN t2 ON t2.col3 = t1.col3
WHERE t2.col3 = 2
ORDER BY date ASC

OR

SELECT
date, t1.col1, t2.col2,
@tmp := t1.col1 * t2.col2 AS `col3`,
@csum := IFNULL(@csum, 0) + @tmp AS `sum`
FROM t1
INNER JOIN t2 ON t2.col3 = t1.col3
WHERE t2.col3 = 2
ORDER BY date ASC

answered Jan 12 '17 at 11:39

Wajih

4,227
2
25
40

1

It may work, but I am not sure it guaranteed to work; I suppose that mysql does not guarantee the order of expression evaluation in the select-part: "As a general rule, other than in SET statements, you should never assign a value to a user variable and read the value within the same statement. ..." (cf. http://dev.mysql.com/doc/refman/5.7/en/user-variables.html) – Stephan Lechner Jan 12 '17 at 11:45
Ah ok, I read it, Thanks for this helpful information :) @StephanLechner – Wajih Jan 12 '17 at 11:49

CalvT · Answer 3 · 2017-01-12T17:17:17.590

While @StephanLechners answer works when working directly with the MySQL database (using tools such as phpmyadmin), in my case when adding his query to my PHP code, it didn't work. So in case someone has a similar problem, here is the query which worked for me.

I'm working with WordPress, so am using $wpdb->get_results to execute the query. This could be one of the reasons Stephans query didn't work for me.

So the query which works is as follows.

SELECT t.date, t.col1, t.col2, t.sum, @runtot:=@runtot + t.sum AS cumulativesum
FROM (SELECT date, t1.col1, t2.col2, t2.col2 * col1 AS `sum` 
FROM t1
INNER JOIN t2 ON t1.col3 = t2.col3
WHERE t2.id = 2) t 
JOIN (SELECT @runtot := 0 AS dummy) dummy 
ORDER BY date

Create a mysql cumulative sum column from generated column

3 Answers3