Union Query - Group by then Sum

[mikejaytlabustro]

I have a Union Query which the result was like this:

Date Cash_In Cash_Out
08/06/2013 100.00
08/06/2013 25.00
08/06/2013 50.00
08/06/2013 20.00

-------------------------------------------------

What I wanted to get is this:

Date Cash_In Cash_Out
08/06/2013 125.00 70.00

-------------------------------------------------
Note: The Date Field comes from two different tables. Original field names are Cash_In_Date and Cash_Out_Date

--------------------------------------------------

Thank You in advance Guys!

 

[plog]

Not possible from that union query. You need to designate which records are In and which are Out. What does your UNION query SQL look like?

 

[mikejaytlabustro]

Not possible from that union query. You need to designate which records are In and which are Out. What does your UNION query SQL look like?

-----------------------------------------------------------------------
It looks like this:

Select Cash_In_Date as Date, Cash_In,"" as Cash_Out FROM CASH_IN
Union
Select Cash_Out_Date as Date,"" as Cash_In, Cash_Out FROM CASH_OUT;

------------------------------------------------------------------------
The Result:

Date Cash_In Cash_Out
08/06/2013 100.00 (In)
08/06/2013 25.00 (In)
08/06/2013 50.00 (Out)
08/06/2013 20.00 (Out)

------------------------------------------------------------------------
The bottom line is that I just want to group the dates between two tables and sum up the total Cash In and Out in two separated fields.

My report should look like this:

Date Cash_In Cash_Out

08/01/2013 100.00 25.00
08/02/2013 250.00 75.00
08/03/2013 85.00
08/04/2013 60.00
08/05/2013 350.00 70.00
08/06/2013 200.00 30.00

 

[plog]

This should be your UNION query:

SELECT Cash_In_Date AS CashDate, Cash_In, 0 AS Cash_Out FROM CASH_IN
UNION ALL
SELECT Cash_Out_Date AS CashDate, 0 AS Cash_In, Cash_Out FROM CASH_OUT;

Name the above query 'subCashTotal'. Then build another query based on it using this SQL:

SELECT CashDate, SUM(Cash_In) AS Cash_In_Tot, SUM(Cash_Out) AS Cash_Out_Tot
FROM subCashTotal
GROUP BY CashDate
ORDER BY CashDate;

 

[mikejaytlabustro]

This should be your UNION query:

SELECT Cash_In_Date AS CashDate, Cash_In, 0 AS Cash_Out FROM CASH_IN
UNION ALL
SELECT Cash_Out_Date AS CashDate, 0 AS Cash_In, Cash_Out FROM CASH_OUT;

Name the above query 'subCashTotal'. Then build another query based on it using this SQL:

SELECT CashDate, SUM(Cash_In) AS Cash_In_Tot, SUM(Cash_Out) AS Cash_Out_Tot
FROM subCashTotal
GROUP BY CashDate
ORDER BY CashDate;

-----------------------------------------------------------------------
Thanks you plog! Problem Solved!

Could I ask another question? Referring to the code above, i use only UNION instead of UNION ALL. I've noticed that the two transactions in 08/03/2013 Cash In at 100.00 each appears only once instead of twice in the subCashTotal. But using UNION ALL, it does the computation correctly. What is the difference between this two commands/codes?

 

[plog]

UNION ALL eliminates duplicate rows, UNION does not.


http://stackoverflow.com/questions/49925/what-is-the-difference-between-union-and-union-all