How to GROUP BY on a UNION query to summarise the UNION ed data set
I am calculating the Savings captured in two levels - booking level OR item level.
My data set is as follows :
Level 1 : Saving is at Booking level. That is one saving amount for the booking as a whole is captured. Joining with Item table resulted saving amount to repeat. But the final report must not consider repeated value when summing up.
Booking Item Type Cost Saving 1 01 A 1000 100 1 02 A 1000 100 1 03 A 1000 100 2 01 B 250 10 2 02 B 250 10 3 01 A 500 50Level 2 : Saving is at item level. That is a saving per item is captured. Below is Booking table joined with Item table.
Booking Item Type Cost Saving 4 01 A 2000 500 4 02 A 1500 200 4 03 B 250 10 5 05 B 750 50In my report, I have an intersect query and a union query to achieve, above level 1 data set and level 2 data set.
Finally as the report output, what I need to achieve is as follows :
Type Cost Saving A 5000 850 B 1250 70What I end up is as below, which is a break down from each level per Type:
Type Cost Saving A 1500 150 A 3500 700 B 250 10 B 1000 60This is because I am not sure how to apply an outer level group by to the query so that each Type results in one row with summed up values. Please let me know how to achieve this?