calculations between arrays of two (or more) tables

[Bin_2]

i made union to two mysql tables

select cashid, sum(amt) as ramt, NULL AS pamt from receipt where cashid in ('17', '23') GROUP BY cashid
    union all
select cashid, NULL AS ramt, sum(amt) as pamt from payment where cashid in ('17', '23') GROUP BY cashid

and now i want to make calculations between them.
like--

balance = ramt - pamt

can anyone suggest me how can i do that?

thanks in advance

[urtrivedi]

select cashid, ramt, pamt, ifnull(ramt,0)-ifnull(pamt,0) balance from (
select cashid, sum(amt) as ramt, NULL AS pamt from receipt where cashid in ('17', '23') GROUP BY cashid
    union all
select cashid, NULL AS ramt, sum(amt) as pamt from payment where cashid in ('17', '23') GROUP BY cashid
)
group by cashid

[Bin_2]

thanks for the reply.

but it is showing error until i made a alias for the derived table like this

SELECT cashid, ramt, pamt, ifnull(ramt, 0)-ifnull(pamt, 0) balance FROM (
SELECT cashid, sum(amt) AS ramt, NULL AS pamt FROM receipt WHERE cashid IN (SELECT aid FROM account WHERE ac_id=6) AND cashid!=1 GROUP BY cashid
UNION ALL
SELECT cashid, NULL AS ramt, sum(amt) AS pamt FROM payment WHERE cashid IN (SELECT aid FROM account WHERE ac_id=6) AND cashid!=1 GROUP BY cashid) AS bal
GROUP BY cashid

but it still is selecting data ONLY FROM 'receipt' NOT FROM 'payment'

here is sqlfiddle
http://sqlfiddle.com/#!2/78bf30/1/0

[urtrivedi]

I am looking into it

[urtrivedi]

SELECT cashid, sum(ramt) ramt, sum(pamt)pamt, ifnull(sum(ramt), 0)-ifnull(sum(pamt), 0) balance
  FROM (
    SELECT cashid, sum(amt) AS ramt, NULL AS pamt
      FROM receipt WHERE cashid IN ('17','23') GROUP BY cashid
  UNION ALL
    SELECT cashid, NULL AS ramt, sum(amt) AS pamt
      FROM payment WHERE cashid IN ('17','23') GROUP BY cashid)
AS bal

GROUP BY cashid

[Bin_2]

wow! thats great. it worked.

thanks.......

now, please, will u guide me how this worked? just want to understand how this query works....

[urtrivedi]

if you run all part by part you can understand

set 1

SELECT cashid, sum(amt) AS ramt, NULL AS pamt
      FROM receipt WHERE cashid IN ('17','23') GROUP BY cashid

set 2

SELECT cashid, sum(amt) AS ramt, NULL AS pamt
      FROM receipt WHERE cashid IN ('17','23') GROUP BY cashid

set 2 = set 1 union set 2

SELECT cashid, sum(amt) AS ramt, NULL AS pamt
      FROM receipt WHERE cashid IN ('17','23') GROUP BY cashid
  UNION ALL
    SELECT cashid, NULL AS ramt, sum(amt) AS pamt
      FROM payment WHERE cashid IN ('17','23') GROUP BY cashid)

now set 3 is base table for us and simple sum and subtraction performed on set 3