CODEWITHSUNDEEP
mysqlsqlcountuniondistinct
Tim
Tim

Reputation: 273

SQL count of distinct union

I have a query that captures customer ids from three tables (each table is a different contact method). I want to get the count of distinct customer ids after the unions.

The SQL statement below is working and returns a list of unique customer ids (no dups):

SELECT DISTINCT customer_id
  FROM email_contact
 WHERE info_id = 1
   AND status = 'SENT' 
UNION
SELECT DISTINCT customer_id
  FROM call_contact
 WHERE info_id = 1
   AND status = 'CALLED' 
UNION  
SELECT DISTINCT customer_id
  FROM mail_contact
 WHERE info_id = 1
   AND status = 'MAILED'

From that query I want a count of customers, but my attempts to wrap the query in a select count keep producing syntax errors. How can wrap the unions to provide me with a count of the clients?

Upvotes: 1

Views: 342

Answers (3)

nbk
nbk

Reputation: 49373

You can wrap it like this

SELECT COUNT(*)
FROM
(        SELECT DISTINCT customer_id
          FROM email_contact
         WHERE info_id = 1
           AND status = 'SENT' 
        UNION
        SELECT DISTINCT customer_id
          FROM call_contact
         WHERE info_id = 1
           AND status = 'CALLED' 
        UNION  
        SELECT DISTINCT customer_id
          FROM mail_contact
         WHERE info_id = 1
           AND status = 'MAILED') t1

Upvotes: 1

GMB
GMB

Reputation: 222432

I would recommend:

SELECT COUNT(DISTINCT customer_id)
FROM (
    SELECT customer_id FROM email_contact WHERE info_id = 1 AND status = 'SENT' 
    UNION ALL SELECT customer_id FROM call_contact WHERE info_id = 1 AND status = 'CALLED' 
    UNION ALL SELECT customer_id FROM mail_contact WHERE info_id = 1 AND status = 'MAILED'
) t

I removed the DISTINCT and I changed the UNIONs to UNION ALL, so the database just gathers all the rows from the 3 union members without attempting to manage duplicates (this is fast). Then, you can use COUNT(DISTINCT ...) in the outer query.

Upvotes: 2

Gordon Linoff
Gordon Linoff

Reputation: 1269583

Is this what you ant?

SELECT COUNT(*)
FROM ((SELECT customer_id
       FROM email_contact
       WHERE info_id = 1 AND status = 'SENT' 
      ) UNION  -- on purpose to remove duplicates
      (SELECT customer_id
       FROM call_contact
       WHERE info_id = 1 AND status = 'CALLED' 
      ) UNION  
      (SELECT customer_id
       FROM mail_contact
       WHERE info_id = 1 AND status = 'MAILED'
      )
     ) c;

Note that all your DISTINCTs are unnecessary because UNION removes duplicates.

Upvotes: 0

Related Questions