Jits Jits - 4 months ago 11
SQL Question

Rolling up addition using mysql

I would like to add 3rd column or modify 2nd column by addition of previous row value score. I'm getting date-wise two table addition but can't get serial addition of data.

DDLs:

CREATE TABLE 1_bugs
( id int(11) NOT NULL
, date date NOT NULL
, cf1 int(11) NOT NULL
, cf2 int(11) NOT NULL
) ENGINE=InnoDB DEFAULT CHARSET=latin1;

INSERT INTO 1_bugs (id, date, cf1, cf2) VALUES
(1, '2016-07-19', 3, 2),
(2, '2016-07-19', 2, 1),
(3, '2016-07-22', 2, 2);


Query:

SELECT table.date1, IFNULL(table.cf1 + bugs.cf2),0) as score FROM table GROUP BY table.date1;


Output:

| date1 | score |
| 2016-07-19 | 5 |
| 2016-07-19 | 3 |
| 2016-07-22 | 4 |


Expected:

| date1 | score | Total score |
| 2016-07-19 | 5 | 5 |
| 2016-07-19 | 3 | 8 |
| 2016-07-22 | 4 | 12 |


I have tried rollup but it doesn't give output as expected and just adds null row with addition of all score values.

| date1 | score |
| 2016-07-19 | 5 |
| 2016-07-19 | 3 |
| 2016-07-22 | 4 |
| null | 12 |


Can someone please help me to get output like expected?

Answer
SELECT x.*
     , x.cf1+x.cf2 sub_total
     , SUM(y.cf1+y.cf2) running
  FROM 1_bugs x 
  JOIN 1_bugs y 
    ON y.id <= x.id 
 GROUP 
    BY x.id;
+----+------------+-----+-----+-----------+---------+
| id | date       | cf1 | cf2 | sub_total | running |
+----+------------+-----+-----+-----------+---------+
|  1 | 2016-07-19 |   3 |   2 |         5 |       5 |
|  2 | 2016-07-19 |   2 |   1 |         3 |       8 |
|  3 | 2016-07-22 |   2 |   2 |         4 |      12 |
+----+------------+-----+-----+-----------+---------+
Comments