Combining Total Data from Two Different Columns in Two Queries

To combine totals from two different columns in separate queries, use a UNION ALL operator to stack the results vertically before applying an outer SUM() aggregate. This approach ensures that distinct column values are treated as a single dataset for final aggregation, avoiding the complexity of joining unrelated aggregates or using complex subqueries.

Understanding the Problem Structure

When you have two separate queries calculating totals for different columns, standard SQL joins often fail because they require a common key to align rows. If the data sources are independent or the columns represent different dimensions (e.g., sales revenue vs. unit count), a direct join may produce incorrect Cartesian products or null values. The goal is not to merge the columns side-by-side but to treat their total values as part of a single list so you can calculate a grand total. Consider a scenario where Query A calculates the total price from an Orders table and Query B calculates the total tax from a Taxes table. These tables might not share a one-to-one relationship suitable for a simple join. Instead, we need to view these two scalar values as rows in a temporary result set.

The Limitation of Direct Joins

Attempting to use a CROSS JOIN on two aggregated subqueries works but can be inefficient and hard to read. It creates a single row with two columns, requiring further manipulation to sum them. For example:

SELECT SUM(PriceTotal) + SUM(TaxTotal)
FROM (
    SELECT (SELECT SUM(Price) FROM Orders) AS PriceTotal,
           (SELECT SUM(Tax) FROM Taxes) AS TaxTotal
) AS Combined;

While this works, it executes two separate scans and requires nested logic. A more elegant and performant solution involves vertical stacking.

Using UNION ALL for Vertical Stacking

The most robust method is to use UNION ALL to combine the results of both queries into a single column. This transforms the horizontal separation into vertical alignment. Once stacked, you can wrap the entire operation in an outer query to apply the final SUM(). Here is the general structure:

SELECT SUM(ColumnTotal) AS GrandTotal
FROM (
    SELECT SUM(Col1) AS ColumnTotal
    FROM TableA
    WHERE ConditionsA
    UNION ALL
    SELECT SUM(Col2) AS ColumnTotal
    FROM TableB
    WHERE ConditionsB
) AS DerivedTable;

This pattern ensures that each subquery returns exactly one row containing its respective total. The UNION ALL merges these two rows into a two-row result set. The outer SUM() then adds these two values together efficiently.

Why UNION ALL Over UNION?

Always prefer UNION ALL over UNION when combining aggregates. UNION attempts to remove duplicate rows by sorting and comparing every value, which adds unnecessary computational overhead. Since you are summing distinct calculated totals, duplicates are rare and irrelevant to the final arithmetic. Using UNION ALL allows the database engine to process the streams without the cost of deduplication.

Handling NULL Values and Data Types

A critical aspect of this technique is handling potential NULL results. If one of the subqueries returns no rows, SUM() will return NULL. In SQL, adding NULL to a number results in NULL, which would invalidate your grand total. To prevent this, wrap each inner SUM() with COALESCE(..., 0) or ISNULL(..., 0) depending on your dialect. This ensures that if a query yields no matches, it contributes zero to the total rather than breaking the calculation.

SELECT SUM(ColumnTotal) AS GrandTotal
FROM (
    SELECT COALESCE(SUM(Col1), 0) AS ColumnTotal
    FROM TableA
    UNION ALL
    SELECT COALESCE(SUM(Col2), 0) AS ColumnTotal
    FROM TableB
) AS DerivedTable;

Additionally, ensure that the data types of Col1 and Col2 are compatible. While most modern databases handle implicit conversion between integers and decimals, explicit casting may be required in strict schemas to avoid type mismatch errors during the union operation.

Performance Considerations

This pattern is generally efficient because it relies on simple sequential scans or index seeks within each subquery. However, if the underlying tables are massive, consider filtering data aggressively in the WHERE clauses of each subquery before aggregation. Reducing the dataset size early minimizes memory usage during the sort phase of the union. For more insights on managing row counts in SQL, see Single vs Multiple Rows in SQL. Understanding how your database handles row sets helps optimize these aggregation patterns further. If your environment involves complex string manipulations within these queries, such as formatting currency before summing, remember that string operations can impact performance. Review best practices for Using Double Quotes in Oracle SQL String to avoid syntax issues when embedding literals in your subqueries.

Alternative Approaches

In some specific database systems like PostgreSQL or SQL Server, you might use lateral joins or window functions, but these add complexity without significant benefit for simple scalar totals. The UNION ALL approach remains the most portable and readable across MySQL, PostgreSQL, SQL Server, and Oracle. Another alternative is using a Common Table Expression (CTE) to define the subqueries separately, improving readability for very long queries:

WITH TotalA AS (
    SELECT SUM(Col1) AS Val FROM TableA
),
TotalB AS (
    SELECT SUM(Col2) AS Val FROM TableB
)
SELECT SUM(Val) FROM (
    SELECT Val FROM TotalA
    UNION ALL
    SELECT Val FROM TotalB
) AS AllTotals;

This does not change the execution plan significantly but makes maintenance easier when the subqueries are reused elsewhere.

Frequently Asked Questions

Can I use UNION instead of UNION ALL?

Yes, but it is inefficient because UNION removes duplicates by sorting the result set. Since you are summing distinct aggregate values, deduplication is unnecessary and slows down execution. Always use UNION ALL for performance.

What happens if one query returns no rows?

If a query returns no rows, SUM() produces NULL. Without COALESCE, the final total becomes NULL. Wrapping the inner sums with COALESCE(sum_col, 0) ensures zero is added instead, preserving the integrity of the grand total.

Does this work with GROUP BY queries?

No, this technique assumes scalar totals (single values per query). If your subqueries return multiple rows due to GROUP BY, UNION ALL will stack all those rows, and the outer SUM() will aggregate everything together, potentially losing group distinctions. You must pre-aggregate to a single row per subquery.

Is there a performance penalty for scanning tables twice?

Each subquery scans its respective table once. This is optimal because the database cannot infer relationships between unrelated columns without explicit joins. Scanning twice is necessary unless you can restructure the schema to unify the data source logically.

How do I handle different data types in the columns?

Ensure both columns are numeric. If one is text representing numbers, cast it explicitly using CAST(col AS DECIMAL). Mismatched types in UNION ALL can cause conversion errors or unexpected implicit conversions that affect precision.