CODEWITHSUNDEEP
sql-serversql-server-2008
offcenter35
offcenter35

Reputation: 145

SQL Server - How to count number of distinct entries across multiple columns?

I have a table similar to this:

FORM_NUM         DOB_1           DOB_2            DOB_3
1                06Jul1973                        20Jan1994
1                06Jul1973       12Mar1980        20Jan1994
2                17Oct1960       12Dec1996        12Dec1996
2                18Feb2000
2                17Oct1960       17Oct1960

I want to count how many distinct birthdays there are for each form. You can consider each row a subset of the form. In this example, there are 3 distinct birthdays for form 1, and 3 distinct birthdays for form 2.

I know the code to count the number of distinct entries per column is COUNT(DISTINCT ...), but sometimes the same birthday is located in multiple columns, so simply summing the distinct counts for each column does not work.

I want output to look like:

FORM_NUM      COUNT
1             3
2             3

What is the best way to accomplish this?

Edit: It works. Code goes something like this...

SELECT FORM_NUM, COUNT(DOB) FROM  

(SELECT FORM_NUM, DOB_1 as DOB FROM table WHERE LEN(DOB_1) > 0
UNION
SELECT FORM_NUM, DOB_2 as DOB FROM table  WHERE LEN(DOB_2) > 0
UNION
SELECT FORM_NUM, DOB_3 as DOB FROM table WHERE LEN(DOB_3) > 0
UNION
SELECT FORM_NUM, DOB_4 as DOB FROM table WHERE LEN(DOB_4) > 0
UNION
SELECT FORM_NUM, DOB_5 as DOB FROM table WHERE LEN(DOB_5) > 0)TMP


GROUP BY FORM_NUM

Upvotes: 2

Views: 281

Answers (2)

SouravA
SouravA

Reputation: 5243

SELECT FORM_NUM, COUNT(DOB) COUNT FROM

(SELECT FORM_NUM, DOB_1 DOB FROM YOURTABLE
UNION
SELECT FORM_NUM, DOB_2 DOB FROM YOURTABLE
UNION
SELECT FORM_NUM, DOB_3 DOB  FROM YOURTABLE) A GROUP BY FORM_NUM

Upvotes: 2

James Z
James Z

Reputation: 12318

You could do something like following:

select FORM_NUM, count(DOB) from (
   select FORM_NUM, DOB_1 as DOB from table where DOB_1 is not null
   union
   select FORM_NUM, DOB_2 as DOB from table where DOB_2 is not null
   union
   select FORM_NUM, DOB_3 as DOB from table where DOB_3 is not null
) TMP
GROUP BY FORM_NUM

This will select each field separately and union will remove duplicate values

Upvotes: 2

Related Questions