Mysql Running Balance by group from different tables

Question

I have a 3 tables that I used to calculate the Balance of my customers, I need the balance to be calculated from the invoice Sale Amount +Invoice handling + Taxes + the total of Amount in table shipping where the invoice number is same with the invoice_No then subtract the sum of the amount from table Income where they have the same Invoice number then + the previous Balance which is the balance if the previous invoice (previous invoice can be found by the date or even by the Id. I expect the result to look like Balance

InVoiNo Cust SaleAmount Handling Taxes Ship Income Last_Balance Balnce
 A1     A           500      300   200 1295   1000         0.00   1295
 A2     A            50       20    30 1860  15000         1295   1755
 B1     B          1000      100    10 1495    100            0   2505
 C1     C           600      277     0  576   1000         0.00    453
 C2     C           600      300   100  636    500          453   1589

This means that I want to see and calculate the previous Balance for every customer from the previous invoice And this is the schema of my tables

CREATE TABLE `income` (
  `Id` int(11) NOT NULL,
  `Invoice_No` varchar(12) NOT NULL,
  `Date` date NOT NULL,
  `Amount` int(11) NOT NULL,
  `Customer` varchar(20) NOT NULL
) ENGINE=MyISAM DEFAULT CHARSET=latin1;

INSERT INTO `income` (`Id`, `Invoice_No`, `Date`, `Amount`, `Customer`) VALUES
(1, 'A1', '2017-08-02', 700, 'A'),
(2, 'A1', '2017-08-02', 300, 'A'),
(3, 'A2', '2017-08-02', 1500, 'A'),
(4, 'B1', '2017-08-02', 30, 'B'),
(5, 'B1', '2017-08-02', 60, 'B'),
(6, 'B1', '2017-08-02', 10, 'B'),
(7, 'C1', '2017-08-02', 500, 'C'),
(8, 'C1', '2017-08-02', 500, 'C'),
(9, 'C2', '2017-08-02', 500, 'C');

CREATE TABLE `invoices` (
  `id` int(11) NOT NULL,
  `InVoice_No` varchar(50) NOT NULL,
  `Date` datetime DEFAULT NULL,
  `Customer` varchar(50) NOT NULL,
  `SaleAmount` decimal(32,2) DEFAULT NULL,
  `Handling` decimal(32,2) DEFAULT NULL,
  `Taxes` decimal(32,2) DEFAULT NULL
) ENGINE=MyISAM DEFAULT CHARSET=utf8;

INSERT INTO `invoices` (`id`, `InVoice_No`, `Date`, `Customer`, `SaleAmount`, `Handling`, `Taxes`) VALUES
(1, 'A1', '2017-08-03 10:19:06', 'A', '500.00', '300.00', '200.00'),
(1, 'D1', '2017-08-03 00:00:00', 'D', '323680.00', '3958.00', '0.00'),
(1, 'A2', '2017-08-03 10:19:06', 'A', '50.00', '20.00', '30.00'),
(1, 'B1', '2017-08-03 10:19:06', 'B', '1000.00', '100.00', '10.00'),
(1, 'C1', '2017-08-03 10:19:06', 'C', '600.00', '277.00', '0.00'),
(1, 'C2', '2017-08-03 10:19:06', 'C', '600.00', '300.00', '100.00'),
(1, 'A3', '2017-08-03 10:19:06', 'A', '60.00', '60.00', '60.00');

CREATE TABLE `shipping` (
  `Id` int(11) NOT NULL,
  `Date` date NOT NULL,
  `Invoice_no` varchar(12) NOT NULL,
  `Ship_Amount` int(11) NOT NULL,
  `Customer` varchar(20) NOT NULL
) ENGINE=MyISAM DEFAULT CHARSET=latin1;

INSERT INTO `shipping` (`Id`, `Date`, `Invoice_no`, `Ship_Amount`, `Customer`) VALUES
(1, '2017-08-09', 'A1', 300, 'A'),
(2, '2017-08-02', 'A1', 500, 'A'),
(3, '2017-08-02', 'B1', 250, 'B'),
(4, '2017-08-03', 'B1', 50, 'B'),
(5, '2017-08-02', 'C1', 125, 'C'),
(6, '2017-08-03', 'C1', 451, 'C'),
(7, '2017-08-02', 'C2', 478, 'C'),
(8, '2017-08-03', 'C2', 158, 'C'),
(9, '2017-08-02', 'B1', 785, 'B'),
(10, '2017-08-03', 'B1', 410, 'B'),
(11, '2017-08-02', 'A1', 45, 'A'),
(12, '2017-08-03', 'A1', 100, 'A'),
(13, '2017-08-02', 'A2', 10, 'A'),
(14, '2017-08-03', 'A3', 60, 'A'),
(15, '2017-08-02', 'A1', 350, 'A'),
(16, '2017-08-03', 'A2', 550, 'A'),
(17, '2017-08-02', 'A2', 150, 'A'),
(18, '2017-08-03', 'A2', 500, 'A'),
(19, '2017-08-02', 'A2', 200, 'A'),
(20, '2017-08-03', 'A2', 450, 'A');


ALTER TABLE `income`
  ADD PRIMARY KEY (`Id`);

ALTER TABLE `invoices`
  ADD PRIMARY KEY (`Customer`,`id`,`InVoice_No`),
  ADD UNIQUE KEY `CalNo` (`InVoice_No`);

ALTER TABLE `shipping`
  ADD PRIMARY KEY (`Id`);


ALTER TABLE `income`
  MODIFY `Id` int(11) NOT NULL AUTO_INCREMENT, AUTO_INCREMENT=10;
ALTER TABLE `shipping`
  MODIFY `Id` int(11) NOT NULL AUTO_INCREMENT, AUTO_INCREMENT=21;
/*!40101 SET CHARACTER_SET_CLIENT=@OLD_CHARACTER_SET_CLIENT */;
/*!40101 SET CHARACTER_SET_RESULTS=@OLD_CHARACTER_SET_RESULTS */;
/*!40101 SET COLLATION_CONNECTION=@OLD_COLLATION_CONNECTION */;

Now now.. While I'm sure those comments have a healthy amount of joking tone when spoken, there's the risk they come across as overly sarcastic in text.. Be Nice to New Users — Caius Jard, Aug 03 '17 at 13:48
Oh, I do wish you'd used SQL Server or Oracle rather than MySQL.. — Caius Jard, Aug 03 '17 at 13:51
Take a look here: https://stackoverflow.com/questions/11303532/simulate-lag-function-in-mysql - most of your query is standard join blah, it's the "previous row balance" that will trip you up. By simulating the LAG() function that SQLServer or oracle use to do this, you can get the previous row's balance. You might need some additional logic e.g. using another variable to detect if the company has changed since the last row, and reset the balance to 0 — Caius Jard, Aug 03 '17 at 14:04
Truly appreciate the reproducible data, very rare on SQL questions. However, questions require earnest attempts where we help troubleshoot issues. This is asking for a code-writing service for us unpaid volunteers. — Parfait, Aug 03 '17 at 14:39

score 0 · Accepted Answer · answered Aug 06 '17 at 21:27

try this

SELECT
    P.id,
    P.`Date`,
    P.Customer,
    P.SaleAmount,
    P.Handling,
    P.Taxes,
    P.InVoice_No,
    @SHIP:=COALESCE(E.s, 0) AS Shipping,
    @INCOME:=COALESCE(S.s, 0) AS Income,
    @BOL:=P.SaleAmount+ P.Handling+P.Taxes+COALESCE(E.s,0) - COALESCE(S.s,0) AS Balance,
    @sum := if(@cat = P.Customer,@sum,0)+@BOL AS CatTotal,
    @cat := P.Customer

    FROM invoices AS P
    LEFT OUTER JOIN (SELECT
    shipping.Customer,
    shipping.Invoice_no,
    SUM(shipping.Ship_Amount) AS s
    FROM shipping
    GROUP BY shipping.Customer, shipping.Invoice_no
    ) AS E ON P.Customer = E.Customer AND P.InVoice_No = E.Invoice_no
    LEFT OUTER JOIN (SELECT
    income.Customer,
    income.Invoice_No,
    SUM(income.Amount) AS s
    FROM income
    GROUP BY income.Customer, income.Invoice_No
    ) AS S ON P.Customer = S.Customer AND P.InVoice_No = S.Invoice_No



    , (select
    @cat := '',
    @sum := 0
     ) AS InitVarsAlias



    GROUP BY P.InVoice_No, P.Customer, P.`Date`
    ORDER BY P.Customer, P.`Date`

Thank you very much @Yahia Baiba the column Previous balance is not shown in your query but i think i can get it by sum-@Bol, Am I right? except this this is working wonderful for me , but the problem it can not be stored as a MySQL View because it contains @ variables — Faucot Celine, Aug 07 '17 at 13:52

Mysql Running Balance by group from different tables

1 Answers1