CODEWITHSUNDEEP
mysqlcumulative-sum
James
James

Reputation: 37

MySQL Cumulative Total

A company rewards one of its employees every day. The data is maintained in a table called REWARDS. As shown in the sample data below, we have one row per day in the table with the following structure:

(Sorry for the poor table formatting)

REWARD_DATE EMP_ID  REWARD_AMT
1-Jan-15    101       400
2-Jan-15    102       300
3-Jan-15    101       700
4-Jan-15    102       500
5-Jan-15    103       100

Can you write a query to report the running totals as the following?

REWARD_DATE #EMP    TOT_REWARD_AMT
1-Jan-15    1            400
2-Jan-15    2            700
3-Jan-15    2           1400
4-Jan-15    2           1900
5-Jan-15    3           2000

Upvotes: 1

Views: 438

Answers (1)

Carsten Massmann
Carsten Massmann

Reputation: 28196

You could do this (slightly simplified now):

SELECT b.rdate, COUNT(distinct a.eid) empcnt, SUM(a.amnt) total
FROM tbl a
INNER JOIN tbl b ON b.rdate>=a.rdate
GROUP BY b.rdate

as demonstrated here: http://sqlfiddle.com/#!9/f6871/2

Upvotes: 2

Related Questions