Count cumulative/increase for each id [MYSQL]

Question

I would like to count an increase values for each record from this below table (table_values):

  id | open | closed | in_progress | project |
  1  | 0    | 0      | 0           | 20      | 
  2  | 1    | 0      | 1           | 20      |
  3  | 1    | 1      | 1           | 55      | 
  4  | 1    | 1      | 1           | 20      | 
  5  | 1    | 1      | 1           | 20      | 
  6  | 2    | 2      | 0           | 20      |

So for example to select where project = 20 results should be:

 id | open | closed | in_progress | project | Total |
 1  | 0    | 0      | 0           | 20      |  0    |
 2  | 1    | 0      | 1           | 20      |  2    |
 4  | 2    | 1      | 2           | 20      |  3    |
 5  | 3    | 2      | 3           | 20      |  3    |
 6  | 5    | 4      | 3           | 20      |  4    |

Select should return cumulative results for each id if possible. Any suggestions?

Regards.

UPDATE: TABLE:

  id | open | 
  1  | 2    |
  2  | 3    |
  3  | 5    |

Result:

  id | open | cumulative_open
  1  | 2    | 2
  2  | 3    | 5
  3  | 5    | 10

My suggestion is that tell us why you want to do this, on what kind of platform, and preferably what you have tried so far. — KIKO Software, Oct 03 '17 at 20:29

score 2 · Answer 1 · answered Oct 03 '17 at 20:35

You can join the rows with all previous (including the same) rows from the same project and use SUM():

select t1.id,
    sum(t2.open) as open,
    sum(t2.closed) as closed,
    sum(t2.in_progress) as in_progress,
    t1.project,
    t1.open + t1.closed + t1.in_progress as Total
from table_values t1
join table_values t2
  on  t2.project = t1.project
  and t2.id <= t1.id
where t1.project = 20
group by t1.id

Demo: http://rextester.com/NZDN42998

This is an expensive query (in terms of performance) - But at least it's reliable.

Chris J · Accepted Answer · 2017-10-03T20:50:25.130

1

This approach uses parameters and looks to achieve what you have described. You might want to amend the names, though, as some are probably a bit unsuitable (as they're closed to reserved names)

SET @open = 0;
SET @closed = 0;
SET @in_progress = 0;
select 
id,
(@open := @open + open) as open,
(@closed := @closed + closed) as closed,
(@in_progress := @in_progress + in_progress) as in_progress,
project,
(open + closed + in_progress) as Total
FROM table_values
where project = 20
group by id;

edited Oct 03 '17 at 20:50

answered Oct 03 '17 at 20:29

Chris J

1,441
9
19

1

note: this pattern with user defined variables depends on behavior which is not guaranteed. From the MySQL Reference Manual: "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.... you might get the results you expect, but this is not guaranteed." Reference: https://dev.mysql.com/doc/refman/5.7/en/user-variables.html – spencer7593 Oct 03 '17 at 20:38
I don't see this as a vulnerability in this instance. The variable isn't being "selected" outside of the operation in which it is set, so there is no ordering issue. – Chris J Oct 03 '17 at 20:41
Why are you using `group by` and `sum`? – Paul Spiegel Oct 03 '17 at 20:46
end of day syndrome. I've edited, thanks – Chris J Oct 03 '17 at 20:50

Count cumulative/increase for each id [MYSQL]

2 Answers2