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

User · Accepted 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

User · Answer

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

[sql] Combine two tables for one output

Examples related to sql

Examples related to union