how to split a field when using union?

Question

Hey all,

I am using this query using UNION, where both fields that contains the amount are combined into one table, and i wanted to split them into 2 fields, first for cash and other for cheque

SELECT exp_cat.cat_name, SUM(exp_cheque.exp_amount) AS Cheque
FROM exp_cat INNER JOIN exp_cheque ON exp_cat.ID = exp_cheque.exp_cat_id
GROUP BY exp_cat.cat_name;
UNION
 SELECT exp_cat.cat_name, SUM(exp_cash.exp_amount) As Cash
FROM exp_cat INNER JOIN exp_cash ON exp_cat.ID = exp_cash.exp_cat_id
GROUP BY exp_cat.cat_name;

Please use this link to understand the each table structure http://stackoverflow.com/questions/3260980/how-to-combine-2-different-table

Answer 1

I'm not sure I understand what you mean but if you want to have the results in different columns this might work:

SELECT exp_cat.cat_name, SUM(exp_cheque.exp_amount) AS Cheque, null as Cash
FROM exp_cat INNER JOIN exp_cheque ON exp_cat.ID = exp_cheque.exp_cat_id
GROUP BY exp_cat.cat_name;
UNION
SELECT exp_cat.cat_name, null as Checque, SUM(exp_cash.exp_amount) As Cash
FROM exp_cat INNER JOIN exp_cash ON exp_cat.ID = exp_cash.exp_cat_id
GROUP BY exp_cat.cat_name;

Answer 2

Select cat_name, sum(cash), sum(cheque) from

(SELECT exp_cat.cat_name, SUM(exp_cheque.exp_amount) AS Cheque,null as cash
FROM exp_cat INNER JOIN exp_cheque ON exp_cat.ID = exp_cheque.exp_cat_id;
UNION
 SELECT exp_cat.cat_name, SUM(exp_cash.exp_amount) As Cash, null as cheque
FROM exp_cat INNER JOIN exp_cash ON exp_cat.ID = exp_cash.exp_cat_id;) 
group by cat_name

Not in front of an IDE right now so I may have a syntax error in there but that is the general idea.

1. Add stub value for cash to the base query returning cheque, and stub value for cheque to the base query returning cash.
2. Add outer query that sums cash and cheque and groups by cat_name.
3. (optional) Can also remove the grouping from the base queries since this will be getting done in the outer query, but it should return the same whether you do this or not.

Answer 3

Select exp_cat.cat_name
    , (Select Sum(c1.exp_amount)
        From exp_cheque As c1
            Inner Join exp_cat As c2
                On c2.Id = c1.exp_cat_id
        Where c2.cat_name = exp_cat.cat_name) As cheque
    , (Select Sum(c1.exp_amount)
        From exp_cash As c1
            Inner Join exp_cat As c2
                On c2.Id = c1.exp_cat_id
        Where c2.cat_name = exp_cat.cat_name) As cash
From exp_cat
Where exp_cat.cat_name Is Not Null
Group By exp_cat.cat_name

If there is a unique constraint on exp_cat.cat_name, then you can eliminate the Group By clause.

Given that this is Access, it may be more efficient in the long run to create two stored queries for cash and cheque grouped by exp_cat.cat_name. Then you could write something like:

Select exp_cat.cat_name
    , Sum( cheque.Total ) As chequeTotal
    , Sum( cash.Total ) As cashTotal
    , Sum( cheque.Total ) + Sum( cash.Total ) As ChequeAndCashTotal
From exp_cat
    Left Join chequeBycat_name As cheque
        On cheque.exp_cat.cat_name = exp_cat.cat_name
    Left Join cashBycat_name As cash
        On cash.exp_cat.cat_name = exp_cat.cat_name
Where exp_cat.cat_name Is Not Null
Group By exp_cat.cat_name

Each of the two queries would look like:

Select exp_cat.cat_name, Sum(exp_cheque.exp_amount) As Total
From exp_cat
    Inner Join exp_cheque
        On exp_cheque.exp_cat_id = exp_cat.Id
Where exp_cat.cat_name Is Not Null
Group By exp_cat.cat_name

Answer 4

If you want totals for cash and cheque returned on the same row for the same cat_name, try something like this:

SELECT cat_name, sum(Cheque), sum(Cash)
(SELECT exp_cat.cat_name, exp_cheque.exp_amount AS Cheque, 0 as cash,
 FROM exp_cat INNER JOIN exp_cheque ON exp_cat.ID = exp_cheque.exp_cat_id
 UNION ALL
 SELECT exp_cat.cat_name, 0 as Cheque, exp_cash.exp_amount As Cash
 FROM exp_cat INNER JOIN exp_cash ON exp_cat.ID = exp_cash.exp_cat_id) sq
GROUP BY cat_name;