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 40 222222 Unknown 25.5
I cannot seem to figure this out. Any help would be greatly appreciated!
EDIT: I need to sum the hours for each ChargeNum/Category combination. I updated the sample data to reflect this.
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.
In your expected output, you've got the second last row sum incorrect, it should be 40 according to the data in your tables, but here is the query:
Select ChargeNum, CategoryId, Sum(Hours)
From (
Select ChargeNum, CategoryId, Hours
From KnownHours
Union
Select ChargeNum, 'Unknown' As CategoryId, Hours
From UnknownHours
) As a
Group By ChargeNum, CategoryId
Order By ChargeNum, CategoryId
And here is the output:
ChargeNum CategoryId
---------- ---------- ----------------------
111111 1 40
111111 2 50
111111 Unknown 70
222222 1 40
222222 Unknown 25.5
Source: Stackoverflow.com