CODEWITHSUNDEEP
sql
Reese
Reese

Reputation: 11

SQL Count Distinct returning one extra count

How is this possible that these two methods are returning different results?

Method 1 (returns correct count):

SELECT COUNT(DISTINCT contact_id)
FROM Traffic_Action
WHERE action_type IN ('Schedule a Tour', 'Schedule Follow-up', 'Lost')

Method 2 (returns one extra count):

SELECT COUNT(DISTINCT CASE WHEN action_type IN ('Schedule a Tour', 'Schedule Follow-up', 'Lost') THEN contact_id ELSE 0 END)
FROM Traffic_Action

Upvotes: 1

Views: 804

Answers (3)

K4M
K4M

Reputation: 952

That means you have 1 record where contact_id is NULL. Normally, COUNT() ignores NULL values. Your second query converts NULL to zero via the "ELSE" branch. That should be why you see a difference.

You can quickly see for yourself in this example. This will return 2 although there are 3 records

select count(distinct a.col1)
from (
    select 1 as Col1
    union select 2
    union select NULL
) a

Upvotes: 0

Jim Macaulay
Jim Macaulay

Reputation: 5141

No wonder you are getting two different results.

First query:

Provides you the distinct count of records where action_type in Schedule a Tour, Schedule Follow-up and Lost

SELECT    COUNT(DISTINCT contact_id) FROM    Traffic_Action WHERE   action_type in 
('Schedule a Tour','Schedule Follow-up','Lost')

Second query:

In this query any value apart from Schedule a Tour, Schedule Follow-up and Lost is considered as 0, and on taking distinct value, results one row according to your case statement

SELECT    COUNT(DISTINCT CASE WHEN action_type in ('Schedule a Tour','Schedule Follow- 
up','Lost') THEN contact_id ELSE 0 END) FROM    Traffic_Action

In simple words,

In first query you are filtering only three values

In second query you have no filters, but case statement on three values and else condition to return 0 for non matching criteria

Upvotes: 1

Fahmi
Fahmi

Reputation: 37473

Remove the else part - as 0 is also counted

SELECT COUNT(DISTINCT CASE WHEN 
    action_type in ('Schedule a Tour','Schedule Follow-up','Lost') THEN contact_id END) 
FROM Traffic_Action

Upvotes: 2

Related Questions