How to count distinct values in SQL union?

Question

I can select distinct values from two different columns, but do not know how to count them. My guess is that i should use alias but cant figure out how to write statement correctly.

$sql =  "SELECT DISTINCT author FROM comics WHERE author NOT IN 
                ( SELECT email FROM bans ) UNION
                SELECT DISTINCT email FROM users WHERE email NOT IN 
                ( SELECT email FROM bans ) ";

Edit1: i know that i can use mysql_num_rows() in php, but i think that takes too much processing.

Answer 1

You could wrap the query in a subquery:

select  count(distinct author)
from    (
        SELECT  author
        FROM    comics 
        WHERE   author NOT IN ( SELECT email FROM bans ) 
        UNION ALL
        SELECT  email 
        FROM    users 
        WHERE   email NOT IN ( SELECT email FROM bans )
        ) as SubQueryAlias

There were two distincts in your query, and union filters out duplicates. I removed all three (the non-distinct union is union all) and moved the distinctness to the outer query with count(distinct author).

Answer 2

You can always do SELECT COUNT(*) FROM (SELECT DISTINCT...) x and just copy that UNION into the second SELECT (more precisely, it's called an anonymous view).