How Can SQL's UNION Operation Consolidate Hours from Multiple Tables?

Combining Tables for a Consolidated Output

In the realm of data manipulation, merging multiple tables into a single cohesive output is a common challenge. Consider the scenario where you have two tables, KnownHours and UnknownHours, each containing charge numbers, categories, months, and hours worked. The goal is to aggregate the hours, disregarding the month, to obtain a consolidated view.

To achieve this, you can leverage the power of SQL's UNION operation. This allows you to combine the results of multiple queries into a single dataset. In this particular case, you need to group the hours by charge number and category, making use of the SUM() aggregate function.

The SQL query to accomplish this is as follows:

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;

Here, the UNION ALL keyword combines the results of two separate queries. The first query retrieves hours from the KnownHours table, grouping them by charge number and category; the second query retrieves hours from the UnknownHours table, treating them as belonging to an 'Unknown' category.

By using UNION ALL, the duplicate checking step is omitted, resulting in better performance compared to using UNION alone.

The result of this query is a consolidated table that provides the total hours worked for each charge number and category, regardless of the month in which they occurred.

The above is the detailed content of How Can SQL's UNION Operation Consolidate Hours from Multiple Tables?. For more information, please follow other related articles on the PHP Chinese website!