Combine two tables for one output

Question

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.

Answer 1

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.

Answer 2

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