How to do a count on a union query

I have the following query:

select distinct profile_id from userprofile_...

union

select distinct profile_id from productions_...

How would I get the count of the total number of results?

Contents hide

Answers:

Thank you for visiting the Q&A section on Magenaut. Please note that all the answers may not help you solve the issue immediately. So please treat them as advisements. If you found the post helpful (or not), leave a comment & I’ll get back to you as soon as possible.

Method 1

If you want a total count for all records, then you would do this:

SELECT COUNT(*)
FROM
(
    select distinct profile_id 
    from userprofile_...

    union all

    select distinct profile_id 
    from productions_...
) x

Method 2

you should use Union All if there are equals rows in both tables, because Union makes a distinct

select count(*) from 
(select distinct profile_id from userprofile_...

union ALL

select distinct profile_id from productions_...) x

In this case, if you got a same Profile_Id in both tables (id is probably a number, so it’s possible), then if you use Union, if you got Id = 1 in both tables, you will lose one row (it will appear one time instead of two)

Method 3

This will perform pretty well:

select count(*) from (
    select profile_id
    from userprofile_...
    union
    select profile_id
    from productions_...
) x

The use of union guarantees distinct values – union removes duplicates, union all preserves them. This means you don’t need the distinct keyword (the other answers don’t exploit this fact and end up doing more work).

Edited:

If you want to total number of different profile_id in each, where given values that appear in both table are considered different values, use this:

select sum(count) from (
    select count(distinct profile_id) as count
    from userprofile_...
    union all
    select count(distinct profile_id)
    from productions_...
) x

This query will out-perform all other answers, because the database can efficiently count distinct values within a table much faster than from the unioned list. The sum() simply adds the two counts together.

Method 4

These will not work if in one of the COUNT(*) the result is equals to 0.

This will be better:

SELECT SUM(total)
FROM
(
    select COUNT(distinct profile_id) AS total
    from userprofile_...

    union all

    select COUNT(distinct profile_id) AS total
    from productions_...
) x

Method 5

As omg ponies has already pointed out that there is no use of using distinct with UNION, you can use UNION ALL in your case…..

SELECT COUNT(*) 
FROM 
( 
select distinct profile_id from userprofile_...
union all
select distinct profile_id from productions_...
) AS t1

Method 6

Best solution is to add count of two query results. It will not be a problem if the table contains large number of records. And you don’t need to use union query.
Ex:

SELECT (select COUNT(distinct profile_id) from userprofile_...) + 
(select COUNT(distinct profile_id) from productions_...) AS total

All methods was sourced from stackoverflow.com or stackexchange.com, is licensed under cc by-sa 2.5, cc by-sa 3.0 and cc by-sa 4.0

0 0 votes

Article Rating