How to do a count on a union query

Breaking down counts

When you are dealing with different tables, you might need a detailed output. Here's how you can break down the counts by each table:

-- Isolation therapy - let's get a count from each table first
SELECT
    'Table1' AS Source,
    COUNT(DISTINCT profile_id) AS DistinctCount
FROM table1
UNION ALL
SELECT
    'Table2' AS Source,
    COUNT(DISTINCT profile_id) AS DistinctCount
FROM table2;


Pay attention to data types

When counting distinct profile_id entries, keep in mind that the data types should match across select statements in the union. Sticky situations with data types could bring in some unexpected surprises! 🎁

d(d)istinct repetitions

Sometimes, you want to count unique entries after combining them. In that case, employ the power of nested union queries:

-- Doing the hard work, so you'll get the glory 🏆 
SELECT COUNT(*)
FROM (
    SELECT DISTINCT profile_id FROM (
        SELECT profile_id FROM table1
        UNION ALL
        SELECT profile_id FROM table2
    ) AS inner_combined
) AS outer_combined;


When complexity calls: Group by

For scenarios that demand a further breakdown of counts, call in the trusty GROUP BY with COUNT() sidekick:

-- Category check! 🐱🐶 
SELECT category, COUNT(*)
FROM (
    SELECT profile_id, 'Category_A' AS category FROM table1
    UNION ALL
    SELECT profile_id, 'Category_B' AS category FROM table2
) AS combined
GROUP BY category;


Subqueries for efficient counting

For large data sets, incapsulating your UNION within a subquery allows the computations to execute faster, since it operates on a reduced set of data:

-- Let your query take a breath with subqueries 💨 
SELECT COUNT(*)
FROM (
    SELECT profile_id
    FROM (SELECT profile_id FROM table1 UNION ALL SELECT profile_id FROM table2) AS sub_union
    GROUP BY profile_id
) AS sub_count;


Tackling complex data structures

With complex data structures and multi-table queries, understanding the interaction JOIN operations have with UNION operations is crucial. The order of execution and the use of parentheses can significantly influence your result.