CODEWITHSUNDEEP
mysqlsql
Amirul Fahmi
Amirul Fahmi

Reputation: 299

Mysql running total from 2 tables

Is it possible to execute subquery running total or cumulative sum from 2 tables? The results will be saved as 'New Value'.

First table :

CREATE TABLE data2 ( date1 DATE,mt int);

INSERT INTO data2 VALUES ('2018-01-01',76564);
INSERT INTO data2 VALUES ('2018-02-01',140005);
INSERT INTO data2 VALUES ('2018-03-01',231973);
INSERT INTO data2 VALUES ('2018-04-01',340085);

Second table:

CREATE TABLE bajet ( date1 DATE,mt int);

INSERT INTO bajet VALUES ('2018-05-01',108344);
INSERT INTO bajet VALUES ('2018-06-01',108349);
INSERT INTO bajet VALUES ('2018-07-01',108338);
INSERT INTO bajet VALUES ('2018-08-01',108329);
INSERT INTO bajet VALUES ('2018-09-01',108311);
INSERT INTO bajet VALUES ('2018-10-01',108325);
INSERT INTO bajet VALUES ('2018-11-01',108330);
INSERT INTO bajet VALUES ('2018-12-01',108331);

Expected Result :

enter image description here

Upvotes: 1

Views: 181

Answers (1)

skelwa
skelwa

Reputation: 585

Assuming the new value field is running total across the two tables based on date, the below query should work for you.

SELECT t.date, t.mt, 
         (@running_total := @running_total + t.mt) AS new_value
FROM (
  SELECT date1 date, mt from data2
  UNION ALL
  SELECT date1 date, mt from bajet
) AS t
JOIN (SELECT @running_total := 0) r
ORDER BY t.date;

Upvotes: 1

Related Questions