0

I have a mysql table and i need a query to have cumulative sum :

hour  col1    col2

00     1000  app
00     400   ck
01     100   app
01     100   ck

I need like this :

 hour  app    ck

00     1000   400
01     1100   500

tried but i am new to mysql so not able to crack looking at other examples:

Divya Nagandla
  • 95
  • 1
  • 13
  • What version of MySQL are you using? – Nick May 16 '19 at 12:29
  • mysql Version 14.14 – Divya Nagandla May 16 '19 at 12:38
  • Highest MySQL version is 8. That might be your distribution. What do you get from `SELECT @@version`? – Nick May 16 '19 at 12:43
  • This functionality is quite new in MySQL, coming in with version 8.0.2 – Kickstart May 16 '19 at 13:16
  • SELECT @@version 5.6.25-73.1-log But if you know the functionality how to use to cumulative sum please let me now that – Divya Nagandla May 16 '19 at 13:32
  • Just solve the problem in python with a simple loop. – Paul Spiegel May 16 '19 at 13:36
  • nope i need in a quer y of mysql:: "https://stackoverflow.com/questions/48618771/cumulative-sum-with-mysql" check this ...i am not getting how to implement in my code – Divya Nagandla May 16 '19 at 13:43
  • Since you're using MySQL5.6 you can't use window functions (`OVER...`). But you can use the answer to this question: https://stackoverflow.com/questions/2563918/create-a-cumulative-sum-column-in-mysql – Nick May 16 '19 at 13:54
  • Nick i m new to mysql can you implement the query usinmg my table which would be better and give me the query – Divya Nagandla May 16 '19 at 14:00
  • DROP TABLE IF EXISTS tests; CREATE TABLE IF NOT EXISTS tests(`hour` char(2) not null primary key, col1 int not null, col2 int not null); INSERT tests(`hour`, col1, col2) VALUES ('00',1000,900),('01',400,100),('02',100,300),('03',100,400); SELECT * FROM tests; SET @col1=0; SET @col2=0; UPDATE tests t INNER JOIN ( SELECT `hour`, @col1:=col1+@col1 AS col1_sum, @col2:=col2+@col2 AS col2_sum FROM tests ORDER BY hour) s ON t.`hour`=s.`hour` SET t.col1=s.col1_sum, t.col2=s.col2_sum ; SELECT * FROM tests; – PeterHe May 16 '19 at 14:08
  • Can not format the query in comments. But the one in the above comment should work – PeterHe May 16 '19 at 14:09

1 Answers1

0

Basic older version of SQL, joining the table against itself.

Something like this

SELECT a.hour, SUM(b.col1), SUM(b.col2)
FROM sometable a
INNER JOIN sometable b
ON a.hour >= b.hour 
GROUP BY a.hour
Kickstart
  • 21,403
  • 2
  • 21
  • 33