CODEWITHSUNDEEP
sqlmysqlunion
grizwako
grizwako

Reputation: 1563

How to count distinct values in SQL union?

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.

Upvotes: 4

Views: 12569

Answers (2)

Andomar
Andomar

Reputation: 238078

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).

Upvotes: 18

chx
chx

Reputation: 11760

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).

Upvotes: 3

Related Questions