Matthew Jones Matthew Jones - 1 month ago 5
SQL Question

Combine two tables for one output

Say I have two tables:

KnownHours:


ChargeNum CategoryID Month Hours
111111 1 2/1/09 10
111111 1 3/1/09 30
111111 1 4/1/09 50
222222 1 3/1/09 40
111111 2 4/1/09 50


UnknownHours:


ChargeNum Month Hours
111111 2/1/09 70
111111 3/1/09 40.5
222222 7/1/09 25.5


I need to group these hours, ignoring Month, into a single data table so that my expected result is the following:


ChargeNum CategoryID Hours
111111 1 90
111111 2 50
111111 Unknown 110.5
222222 1 50
222222 Unknown 25.5


I cannot seem to figure this out. Any help greatly appreciated!

EDIT: I need to sum the hours for each ChargeNum/Category combination. I updated the sample data to reflect this.

lc. lc.
Answer

You'll need to use UNION to combine the results of two queries. In your case:

SELECT ChargeNum, CategoryID, SUM(Hours)
FROM KnownHours
GROUP BY ChargeNum, CategoryID
UNION ALL
SELECT ChargeNum, 'Unknown' AS CategoryID, SUM(Hours)
FROM UnknownHours
GROUP BY ChargeNum

Note - If you use UNION ALL as in above, it's no slower than running the two queries separately as it does no duplicate-checking.

Comments