Distinct Count in SQL Query

I think you need a GROUP BY 1 at the end of that.

You MUST have the FROM clause before the WHERE clause

and, you do need a GROUP BY clause too

USE Clarity;

SET TRANSACTION ISOLATION LEVEL READ UNCOMMITTED;

IF OBJECT_ID('tempdb..#T0') IS NOT NULL
      DROP TABLE #T0
GO
SELECT
      fdays.PAT_ENC_CSN_ID
    , COUNT(*)

FROM F_IP_HSP_PAT_DAYS fdays
      INNER JOIN CLARITY_DEP dep ON fdays.DEPARTMENT_ID = dep.DEPARTMENT_ID
      INNER JOIN CLARITY_DEP_2 dep2 ON dep.DEPARTMENT_ID = dep2.DEPARTMENT_ID

WHERE (fdays.CALENDAR_DT >= '2015-03-01 00:00:00'
      AND fdays.CALENDAR_DT < '2015-09-01 00:00:00')
      AND dep2.ICU_DEPT_YN IS NOT NULL
      AND dep2.ICU_DEPT_YN = 'Y'

GROUP BY
      fdays.PAT_ENC_CSN_ID

Select allOpen in new window

I am a little confused by your question title however. When you use GROUP BY you will get "distinct rows" for the columns you specify (here it s just the one column).

e.g.
table1
PAT_ENC_CSN_ID
100
100
100
100

select PAT_ENC_CSN_ID, count(*) as numrows from table1 GROUP BY PAT_ENC_CSN_ID

result:
PAT_ENC_CSN_ID numrows
100                              4

is this what you meant by distinct count?

>>It says Incorrect syntax near the keyword 'FROM'.

Your FROM needs to be right after the SELECT clause (before the WHERE clause).

SQL clauses for queries must follow this sequence:

SELECT - FROM - WHERE - GROUP BY - HAVING - ORDER BY

And to sear this order into your brain, this mnemonic was learned in one of my few formal  SQL Server classes.  I deeply regret doing this to you, but it's the only one I know of.  If anyone can come up with something more friendly, please let me know...

SWEATY  - FEET  - WILL  - GIVE - HORRIBLE - ODORS

from article by Jim Horn

Also, this condition:
AND dep2.ICU_DEPT_YN IS NOT NULL

Is redundant.

Thank you everyone. Thank you Missus yes I did know I needed the group by, just didn't have it on there.  Thank you Anthony for pointing out what should have been obvious to me.

Thanks Eric and Paul for showing me my beginner mistake, duh.

Paul:  You are correct and you get what I am trying to do.  I want to count the Pat Enc CSN ID only once if it shows in the table.  Right now after modifying my query  by moving the FROM clause, I am getting a result like this:
P_E_CSN_ID, 2
P_E_CSN_ID1, 25
P_E_CSN_ID2, 43
where it is showing me each CSN_ID and how many times it is in the table.

What I really want is a count of distinct CSN ID's,
so just return 19,432 or what ever the number is of how many different times people came into the ICU during a certain time frame, but only count the person once for each time.  

So each time a person comes in they get a new CSN.  But only one for the entire visit.  So even if they are there 3 days they only have one CSN the entire time.

The next time they come in, they get a new CSN.  So count that one cause it is different.

Etc. etc.

It seems like it would be simple but I am having difficulty.

can you give us a sample of your data (in Excel format maybe) and the according wanted results?

yes, here is the spreadsheet.
Clabsi_Count.xls

No, course not!
Here is my query:
 USE Clarity;
 
 SET TRANSACTION ISOLATION LEVEL READ UNCOMMITTED;
 
 IF OBJECT_ID('tempdb..#T0') IS NOT NULL
 DROP TABLE #T0
 GO
 select count(*) from (select distinct PAT_ENC_CSN_ID FROM F_IP_HSP_PAT_DAYS fdays) as A
 
WHERE
(fdays.CALENDAR_DT>={ts '2015-03-01 00:00:00'}
 AND fdays.CALENDAR_DT<{ts '2015-09-01 00:00:00'})
AND dep2.ICU_DEPT_YN='Y'

GROUP BY fdays.PAT_ENC_CSN_ID
when I changed it now I am getting these errors:

Msg 4104, Level 16, State 1, Line 4
The multi-part identifier "fdays.CALENDAR_DT" could not be bound.
Msg 4104, Level 16, State 1, Line 5
The multi-part identifier "fdays.CALENDAR_DT" could not be bound.
Msg 4104, Level 16, State 1, Line 8
The multi-part identifier "fdays.PAT_ENC_CSN_ID" could not be bound.

You need to apply your WHERE clause to the inner query:

 select count(*) 
from (
   select distinct PAT_ENC_CSN_ID 
   FROM F_IP_HSP_PAT_DAYS fdays
   INNER JOIN CLARITY_DEP dep ON fdays.DEPARTMENT_ID = dep.DEPARTMENT_ID
   INNER JOIN CLARITY_DEP_2 dep2 ON dep.DEPARTMENT_ID = dep2.DEPARTMENT_ID
   WHERE fdays.CALENDAR_DT>= '2015-03-01 00:00:00'
   AND fdays.CALENDAR_DT< '2015-09-01 00:00:00'
 AND dep2.ICU_DEPT_YN='Y'
) as A

Select allOpen in new window

Wait I think I see what you did now.  I think it works!

Now to figure out how to get this inside my other queries.....