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 */;
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`