I have 2 following tables in MySQL 8:

Fiddle URL: https://www.db-fiddle.com/f/fejdb5mcCm4TUFDacU3R3M/0

Table1

SELECT * FROM table1

and Table2

SELECT * FROM table2

I would like to generate a Balance column as follows on a union select for company=1, it means balance is: last row balance credit - debit

SELECT id, credit, debit, company, SUM(credit - debit) OVER (ORDER BY id ROWS BETWEEN 1 PRECEDING AND CURRENT ROW) AS balance FROM (
SELECT id, credit, debit, company FROM table1 
UNION ALL
SELECT id, credit, debit, company  FROM table2  
) AS u WHERE company=1

The results of the balance are incorrect (I have added the correct balance manually), what is wrong with my approach and how can I solve it?

DDLs:

CREATE TABLE `table1` (
  `id` int(2) NOT NULL AUTO_INCREMENT,
  `credit` int(4) DEFAULT NULL,
  `debit` int(4) DEFAULT NULL,
  `company` int(1) DEFAULT NULL,
  PRIMARY KEY (`id`)
) ENGINE=InnoDB AUTO_INCREMENT=8 DEFAULT CHARSET=utf8;

/*Data for the table `table1` */

insert  into `table1`(`id`,`credit`,`debit`,`company`) values 
(1,100,0,1),
(2,200,0,1),
(4,100,0,1),
(5,10000,0,2),
(7,50,0,1);

CREATE TABLE `table2` (
  `id` int(2) NOT NULL AUTO_INCREMENT,
  `credit` int(4) DEFAULT NULL,
  `debit` int(4) DEFAULT NULL,
  `company` int(1) DEFAULT NULL,
  PRIMARY KEY (`id`)
) ENGINE=InnoDB AUTO_INCREMENT=9 DEFAULT CHARSET=utf8;

/*Data for the table `table2` */

insert  into `table2`(`id`,`credit`,`debit`,`company`) values 
(1,0,50,1),
(2,0,100,1),
(3,0,50,1),
(7,0,30000,2),
(8,0,200,1);

CodePudding user response：

The problem is we have no way to discern in what order the ID's should be placed to calculate the running total.

We could do something like this where we control which table we use first; but a better approach would be to use a transaction date/time field so we can order by it after ID thus giving you the true order of events.

Lemon asked a similar question earlier in comments, I just showed how this could be implemented and why we struggle with the order in your expected results. Since your expected results do not match the results of this; reconsider your expected results. Either indicate order isn't important or identify how we can order (by t1 or t2 first.., or by a datetime field undefined following ID...)

SELECT id, credit, debit, company, SUM(credit - debit) OVER (partition by company ORDER BY id, src) AS balance FROM (
SELECT id, 't1' src, credit, debit, company FROM table1 
UNION ALL
SELECT id, 't2' src, credit, debit, company  FROM table2  
) AS u WHERE company=1