Partial sum and subtraction (minus) in UNION Query

Question

I have two similar tables (one for bills, the other payments) right now I show to users a union mixing data from both..

Table Bills                             
CustomerId  Amount                      
1           100                        
2           100                        
1           100                        
2           100

Table Payments
CustomerId  Amount
1           100
2           100
1           100

Right now my users can see the following informtation

From Customer 1
Type    CustomerId   Amount
B       1            100
P       1            -100
B       1            100
P       1            -100
TOTAL 0

From Customer 2
Type    CustomerId   Amount
B       1            100
P       1            -100
B       1            100
Total 100

Everything works fine using UNION Statement

Right now I need to show Partial Balance on each record so users can keep in mind balanca while they are looking at records..

Like this... (DESIRED)

From Customer 1
Type    CustomerId   Amount    Partial
B       1            100       100
P       1            -100      0
B       1            100       100
P       1            -100      0

I've alredy try using Variables like @Partial := @Partial + Amount but it seems that it first sum the first part (bills) and then the rest (payments)... like this...

From Customer 1
Type    CustomerId   Amount    Partial
B       1            100       100
P       1            -100      200
B       1            100       200
P       1            -100      100

it seems that first sum everything from bills and then start subtraction... anyone knows how to solve it?

****** // update // ********

here original query ...

(SELECT 'Bill' as cType , b.type, b.tal , 'Customer', b.number , b.date , b.subtot, b.tax, IF(b.type='CA' or b.type='CB' or b.type='CC' or b.type='CX',b.total*-1,b.total) as total FROM bills b WHERE b.idcustomer='000140') UNION ALL (SELECT 'Payment' as cType, 'CO' , '1' , '' , c.idcash , c.date , 0 ,0 , -c.amount FROM cash c WHERE c.idcustomer='000140' and ( c.type='CO' or c.type='DM') ) order by date asc;

this brings something like this

Bill FX 1 Customer 9 2011-02-25 0.00 0.00 100.00

Payment CO 1 37 2011-03-04 0.00 0.00 -100.00

Bill FX 1 Customer 616 2011-03-23 0.00 0.00 100.00

Payment CO 1 751 2011-04-12 0.00 0.00 -100.00

Bill FX 1 Customer 1267 2011-04-27 0.00 0.00 100.00

Payment CO 1 1157 2011-05-10 0.00 0.00 -100.00

Bill FX 1 Customer 1974 2011-05-26 0.00 0.00 100.00

Payment CO 1 1654 2011-06-08 0.00 0.00 -100.00

then When I try to sum patiars...using the following code

set @running_total=0; (SELECT 'Bill' as cType , b.type, b.tal , 'Customer', b.number , b.date , b.subtot, b.tax, IF(b.type='CA' or b.type='CB' or b.type='CC' or b.type='CX',b.total*-1,b.total) as total, ( @running_total := @running_total + total) AS RunningTotal FROM bills b WHERE b.idcustomer='000140') UNION ALL (SELECT 'Payment' as cType, 'CO' , '1' , '' , c.idcash , c.date , 0 ,0 , -c.amount, ( @running_total := @running_total-c.amount) AS RunningTotal FROM cash c WHERE c.idcustomer='000140' and ( c.type='CO' or c.type='DM') ) order by date asc;

results...

Bill FX 1 Customer 9 2011-02-25 0.00 0.00 100.00 100.00

Payment CO 1 37 2011-03-04 0.00 0.00 -100.00 1905.00

Bill FX 1 Customer 616 2011-03-23 0.00 0.00 100.00 200.00

Payment CO 1 751 2011-04-12 0.00 0.00 -100.00 1805.00

Bill FX 1 Customer 1267 2011-04-27 0.00 0.00 100.00 300.00

Payment CO 1 1157 2011-05-10 0.00 0.00 -100.00 1705.00

As you Can See seems to sum first all from bills and then start substraccion from payments...

What you want is a running total. Also, make sure you want to use `UNION` and not `UNION ALL`. — Kermit, Oct 23 '12 at 18:52
I'd like to do it on the fly by the MySql Query .. I can do it App's code but maybe someone knows how to solve it.. I'll coppy alter the mysql query I'm using right now so it's easy to understand. — Ppp, Oct 23 '12 at 19:35
So you're aware `UNION` will remove duplicates? This is perfectly doable, take a look at [this](http://stackoverflow.com/questions/664700/calculate-a-running-total-in-mysql), [this](http://stackoverflow.com/questions/2152503/running-total-for-each-entry-in-group-by), and [this](http://stackoverflow.com/questions/8359077/mysql-running-total-with-count). — Kermit, Oct 23 '12 at 19:37
SQL tables are inherently unordered. You cannot do a running sum unless you have an indication of the ordering of the records. Since there are two different sources, this would preferably be a timestamp. — Gordon Linoff, Oct 23 '12 at 19:50

score 0 · Answer 1 · edited Feb 13 '13 at 00:50

0

The following is the only way I can think of doing this in MySQL, assuming that you have a time stamp for ordering the records. If you have even a moderate amount of data, this may prove to be too inefficient:

select t.*,
       ((select sum(amount)
         from bills b
         where b.customerId = t.customerId and
               b.datetime <= t.datetime
        ) -
        (select sum(amount)
         from payments p
         where p.customerid = t.customerid and
               p.datetime <= t.datetime
        )
       ) as balance
from ((select 'B' as type, customerid, amount, datetime from bills b) union all
      (select 'P', customerid, amount, datetime from payments p)
     ) t

edited Feb 13 '13 at 00:50

Leniel Maccaferri

100,159
46
371
480

answered Oct 23 '12 at 19:54

Gordon Linoff

1,242,037
58
646
786

here I paste original query (not making a sum) – Ppp Oct 23 '12 at 21:25
(SELECT 'Bill' as cType , b.type, b.tal , b.name, b.number , b.date , b.subtot, b.tax, IF(b.type='CA' or b.type='CB' or b.type='CC' or b.type='CX',b.total*-1,b.total) as total FROM bills b WHERE b.idcustomer='000140') UNION ALL (SELECT 'Payment' as cType, 'CO' , '1' , '' , c.idcash , c.date , 0 ,0 , -c.amount FROM cash c WHERE c.idcustomer='000140' and ( c.type='CO' or c.type='DM') ) order by date asc; – Ppp Oct 23 '12 at 21:26
in original post you can see at the end of text... original query and result and query using running_total... – Ppp Oct 23 '12 at 21:35
Keep in mind that result will always be beetwen 20-100 records (no performance expensive) – Ppp Oct 23 '12 at 21:36

Partial sum and subtraction (minus) in UNION Query

1 Answers1