A simple way to sum a result from UNION in MySQL

sql

join

best-practices

performance

byNikita Barsukov·Jan 28, 2025

To sum a UNION result in MySQL, use a nested query. Use UNION ALL to include duplicates, or UNION to exclude them. Here's the blueprint:

SELECT SUM(value) AS total
FROM (
    SELECT value FROM table1
    UNION ALL
    SELECT value FROM table2
) AS union_query;

To get the summed total, look at SUM(value) and union_query is the alias of the inner UNION operation.

Grouping and summing per unique identifier

Adding a GROUP BY clause will allow us to calculate sums per unique identifier (for example, id), providing us with a more refined result:

SELECT id, SUM(value) AS total
FROM (
    SELECT id, value FROM table1
    UNION ALL
    SELECT id, value FROM table2
) AS union_query
GROUP BY id;

This will compute the total sum for each unique id across both tables. Experiment with summing before or after UNION ALL operation for optimizing query performance.

Using subqueries and Common Table Expressions (CTEs)

Considering you have more complex UNION operations, the use of subqueries or Common Table Expressions (CTEs) can significantly enhance your query's clarity:

WITH CTE AS (
    SELECT id, value FROM table1
    UNION ALL
    SELECT id, value FROM table2
)
SELECT id, SUM(value) AS total FROM CTE GROUP BY id;

CTEs can make your query modular, acting as temporary views. Before implementing this, make sure your version of MySQL supports CTEs.

Balancing between performance and accuracy

Every UNION operation comes with its own performances implication. Things to consider:

Grouping before UNION: Could reduce cost if you have well-planned indices.
Grouping after UNION: A must when computing sums across different tables.
Data types and column consistency are critical to prevent implicit casting and to ensure accurate sums.

Assess the context of your database workload for your UNION strategy.

Optimizing your query

Performance could be significantly improved by implementing loose index scans and taking advantage of composite indexes where required.

Watch out for duplicates

Remember that UNION eliminates duplicates whilst UNION ALL keeps them by default. This could lead to unexpected results, so make a conscious choice based on your data's characteristics.

Craft your SQL query with aliases

When constructing your SQL queries, especially with UNION ALL, aliases are critical for readability and context. Think of it as naming your pet, much more fun and personal 🐶.

SELECT a.id, SUM(a.value) AS total_amount
FROM (
    SELECT id, value FROM table1
    UNION ALL
    SELECT id, value FROM table2
) AS a
GROUP BY a.id;

Error handling and best practices

Prevent UNION-related errors by assuring the correct structure and sequence in your SELECT statements. You know they say, prevention is better than cure.

Additionally, maintain only FULL_GROUP_BY SQL mode to prevent potential misuse of aggregate functions.

explain-codes / Sql / A simple way to sum a result from UNION in MySQL

Linked

Using union and count(*) together in an SQL query



Select * from subquery



Mysql SELECT DISTINCT multiple columns



Is there any difference between GROUP BY and DISTINCT



Using an Alias column in the where clause in PostgreSQL