51

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?

OMG Ponies
  • 325,700
  • 82
  • 523
  • 502
David542
  • 104,438
  • 178
  • 489
  • 842

6 Answers6

89

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
Taryn
  • 242,637
  • 56
  • 362
  • 405
24

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)

danronmoon
  • 3,814
  • 5
  • 34
  • 56
Gonzalo.-
  • 12,512
  • 5
  • 50
  • 82
9

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.

Community
  • 1
  • 1
Bohemian
  • 412,405
  • 93
  • 575
  • 722
  • Well, you're considering that profile_id will be unique.. If I got 3 id = 1 in productions (could be a FK), and one Id = 1 in userProfile (could be a PK), then the union will give 1 row, instead of 4 (with both distinct will get 2). Then the count will fail. In the query he posted, he will get a id=1 in each select, the count should be 2 – Gonzalo.- Jul 31 '12 at 01:16
  • @ElVieejo That's what I thought he wanted. Edited answer – Bohemian Jul 31 '12 at 06:00
6

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
Jorge
  • 61
  • 1
  • 1
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
Akash KC
  • 16,057
  • 6
  • 39
  • 59
  • 1
    DISTINCT is useless on a `UNION` - it'd only be necessary for `UNION ALL`. Might not want to copy others answers ;) – OMG Ponies Jul 31 '12 at 00:52
  • 1
    @OMGPonies : BTW, i have not the copy the answer....I got late with fraction of second to post the answer..... – Akash KC Jul 31 '12 at 00:57
5

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
Paulson Peter
  • 574
  • 4
  • 12