How Can I Combine Two Tables with Similar Structures but Different Categories into a Single Output Table?

Combining Two Tables for a Single Output

Database operations often require merging data from multiple sources into a cohesive output. A common challenge is combining tables with similar structures but different categories. Consider the following problem:

You have two tables, "KnownHours" and "UnknownHours," with columns including ChargeNum (a unique identifier), CategoryID (a category assignment), Month (a date), and Hours (the actual value). The task is to combine these tables, ignoring the Month column, into a single data table, grouping the Hours by ChargeNum and CategoryID.

The expected output is:

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

To achieve this, we can utilize the UNION operator, which combines the results of two queries. For this problem, we'll construct two queries:

Query 1:

SELECT ChargeNum, CategoryID, SUM(Hours)
FROM KnownHours
GROUP BY ChargeNum, CategoryID

This query retrieves the ChargeNum, CategoryID, and the sum of Hours for each combination from the "KnownHours" table.

Query 2:

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

This query retrieves the ChargeNum and the sum of Hours for each entry in the "UnknownHours" table, assigning 'Unknown' as the CategoryID.

Combining both queries using UNION ALL produces the desired output:

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

The UNION ALL operator merges the results without removing duplicates, providing a complete view of the combined data.

The above is the detailed content of How Can I Combine Two Tables with Similar Structures but Different Categories into a Single Output Table?. For more information, please follow other related articles on the PHP Chinese website!