Ludwig Arcache Ludwig Arcache - 3 months ago 12
PHP Question

Running balance with mySQL using UNION between tables

need some help with this, I have a table for the money in (payment received) and another table for the money out (expenses), here are a simplified format of the tables :

table : money_in
+-------+-----------+-----------+------------+
| id | invoiceID | amount | Date |
+-------+-----------+-----------+------------+
| 1 | 120 | 1200 | 10-08-2016 |
| 2 | 120 | 1000 | 09-08-2016 |
| 3 | 453 | 750 | 05-08-2016 |
| 4 | 111 | 400 | 02-08-2016 |
| 5 | 111 | 800 | 01-08-2016 |
+-------+-----------+-----------+------------+

table : money_out
+-------+-----------+-----------+------------+
| id |purchaseID | amount | Date |
+-------+-----------+-----------+------------+
| 1 | 142 | 20 | 08-08-2016 |
| 2 | 142 | 50 | 07-08-2016 |
| 3 | 323 | 150 | 06-08-2016 |
| 4 | 344 | 100 | 04-08-2016 |
| 5 | 220 | 100 | 03-08-2016 |
+-------+-----------+-----------+-----------+


I'm creating a statement of account sheet, to display the running balance after each money in/out transaction. I used a query to record the UNION of bothe tables as follow :

$query = "SELECT p.*, i.invoiceID AS transactionID, i.invoiceNumber AS transactionName, i.clientID, i.type AS transactionType, c.id AS entityID, c.clientName FROM " . $wpdb->prefix . 'qi_payments' . " p , " . $wpdb->prefix . 'qi_invoices'. " i, " . $wpdb->prefix . 'qi_clients'. " c WHERE p.invoiceID = i.invoiceID AND i.clientID = c.id
UNION ALL
SELECT pp.*, pur.purchaseID, pur.purchaseNumber, pur.supplierID, pur.type, s.id, s.supplierName FROM " . $wpdb->prefix . 'qi_purchases_payments' . " pp , " . $wpdb->prefix . 'qi_purchases'. " pur, " . $wpdb->prefix . 'qi_suppliers'. " s WHERE pp.purchaseID = pur.purchaseID AND pur.supplierID = s.id ORDER BY paymentDate DESC";


Now I have a table displayed properly but without the last column (Running balance), while it should look like this :

+-------+-----------+-----------+------ -----+
| In | Out | Balance | Date |
+-------+-----------+-----------+------ -----+
| 1200 | | 3730 | 10-08-2016 |
| 1000 | | 2530 | 09-08-2016 |
| | 20 | 1530 | 08-08-2016 |
| | 50 | 1550 | 07-08-2016 |
| | 150 | 1600 | 06-08-2016 |
| 750 | | 1750 | 05-08-2016 |
| | 100 | 1000 | 04-08-2016 |
| | 100 | 1100 | 03-08-2016 |
| 400 | | 1200 | 02-08-2016 |
| 800 | | 800 | 01-08-2016 |
+-------+-----------+-----------+-----------+


I'm puzzled how to produce the Balance values, in SQL query or a PHP script.

Answer

Use @ variable, Like this:

select *
  from (
    select A.*,@sum:=@sum+zsum
      from (
            select *,amount as zsum from money_in
            union all
            select *,-amount from money_out
           ) A, (select @sum:=0) B
     order by id asc
  ) A
 order by id desc
Comments