Can Someone Explain Distinct to Me? 1

[txdave35]

Obviously, I am missing something here. I am trying to get a count of each unique SSN in my table. The table may contain several records for the same SSN.

I am confused why these two queries give different counts:

Query A

select count(distinct mbr_ssn_nbr) from dsnp.pr01_t_mbr_hist
where empr_plan_cd in ('N', 'K')
and mbr_hist_svc_cr_dt = '2009-01-31'

Query B

select distinct count (mbr_ssn_nbr) from dsnp.pr01_t_mbr_hist
where empr_plan_cd in ('N', 'K')
and mbr_hist_svc_cr_dt = '2009-01-31'


What is the difference between the two uses of Distinct?

Thanks!

 

[JulianUK]

The first case will give you a count of the number of unique mbr_ssn_nbr values within your result set, which is I presume what you are after.

In the second case, your use of distinct is irrelevant, as the COUNT statement will return only 1 row, so you are effectively asking for a distinct set of unique "counts", out of a group of what will only be one value.

Julian

 

[txdave35]

Thanks Julian. Makes sense now. I am puzzled on one more thing. Consider this table:


SSN EMPR_PLAN_CD

11122 K
11122 K


Now my query:

select count (empr_plan_cd) as total
from dsnp.pr01_t_mbr_hist
where mbr_hist_svc_cr_dt = '2009-01-31'
and hist_categ_cd = '10'
and rfnd_type_cd = '00'



The count comes back at 1. Why would it not be 2?

 

[jbenson001]

Since we don't have all the data in all the columns you are selecting, we can only assume that it is something in the Where clause, most likely the date column. Remove one piece of the where clause at a time to find out which one is causing the problem.

 

[JulianUK]

As jbenson001 said, something in your WHERE clause must be excluding one row from your result set.

That said, the above query would not work if we consider the table to be as you said. 2 columns, none of which match anything in your where clause!

Another possibility is that you have in fact got a DISTINCT in your actual query? Therefore, there is only 1 distinct empr_plan_cd?

Can't offer anything more without the facts.

Julian