How to do a count on a union query

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

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 ALLselect 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 lost one row (it will appear one time instead of two)

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.