Need sql query for running totals [duplicate]

Question

I have an application where the tasks are logged by the members:

task_id|task_date|task_name|task_duration

On a given day (task_date) there could be more than 1 tasks and sum total of the duration the tasks took collectively. Eg:

Table:2

task_date  | task_count | task_duration
2018-02-24 |    2       | 240
2018-02-25 |    1       | 100
2018-02-26 |    3       | 180
2018-02-27 |    2       | 60

I would like to draw a graph with cumulative figures so that I get a cumulative running total like this:

Desired output:

task_date  | task_count | task_duration
2018-02-24 |     2      | 240
2018-02-25 |     3      | 340
2018-02-26 |     6      | 520
2018-02-27 |     8      | 580

So that I can see the trend of tasks being logged and total time being spent of them.

Please focus on the Table:2 as the data to be worked on.

@UdayrajDeshmukh this question is about mysql, the link you posted is about sql-server. — McNets
– McNets, Commented Feb 27, 2018 at 12:14
@UdayrajDeshmukh but it is another RDBMS, MySql works completely diferent from SQL-Server for cumulative sums. — McNets
– McNets, Commented Feb 27, 2018 at 12:18
@McNets the accepted answer stackoverflow.com/a/2120639/2548147 in the link UdayrajDeshmukh posted is cross database (if you remove SQL-servers @ table sign) and should also work in MySQL — Raymond Nijland
– Raymond Nijland, Commented Feb 27, 2018 at 12:19

McNets · Accepted Answer · 2018-02-27 12:23:20Z

0

You can use a variable for this job.

select task_date, task_count, cum_sum
from (select task_date, task_count, 
             @cum := @cum + task_duration, @cum as cum_sum
      from   tbl, (select @cum := 0) c) r

task_date  | task_count | cum_sum
:--------- | ---------: | :------
2018-02-24 |          2 | 240    
2018-02-25 |          1 | 340    
2018-02-26 |          3 | 520    
2018-02-27 |          2 | 580

dbfiddle here

answered Feb 27, 2018 at 12:23

McNets

10.9k3 gold badges39 silver badges70 bronze badges

Sign up to request clarification or add additional context in comments.

Collectives™ on Stack Overflow

Need sql query for running totals [duplicate]

1 Answer 1

Comments

Linked

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

Comments

Linked

Related