CODEWITHSUNDEEP
sql-server-2008
sam
sam

Reputation: 1294

count(distinct col1) not giving correct count

I have a below table

id date1

1 1973-07-06

1 null

1 1994-01-20

1 1973-07-06

1 1980-03-12

1 1994-01-20

2 1960-10-17

2 1996-12-12

2 1996-12-12

2 2000-02-18

2 null

2 null

2 1960-10-17

2 1960-10-17

2 null

I need to find unique date1 value count for each id

Query i am using is as follows

select id,count(distinct date1) as col1
from iop
group by id

this query is giving me the wrong result as

id col1

1 4

2 4

However I should get

id col1

1 3

2 3

Upvotes: 0

Views: 30

Answers (1)

Sergio Teixeira
Sergio Teixeira

Reputation: 92

the query is counting the null value, try this:

select id,count(distinct date1) as col1
from iop
WHERE date1 NOT LIKE  'NULL'
group by id

Upvotes: 1

Related Questions