Distinct Count in SQL Query

I want to count the patient visits for a certain time frame in a SQL query.  I tried the query below but I keep getting an error message about the FROM clause.  Can someone see my error?  It says Incorrect syntax near the keyword 'FROM'.
And I need to do a Distinct Count 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(*)

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 IS  NOT  NULL  AND dep2.ICU_DEPT_YN='Y'
 
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
Becky EdwardsEpic Clarity DeveloperAsked:
Who is Participating?

[Product update] Infrastructure Analysis Tool is now available with Business Accounts.Learn More

x
I wear a lot of hats...

"The solutions and answers provided on Experts Exchange have been extremely helpful to me over the last few years. I wear a lot of hats - Developer, Database Administrator, Help Desk, etc., so I know a lot of things but not a lot about one thing. Experts Exchange gives me answers from people who do know a lot about one thing, in a easy to use platform." -Todd S.

Missus Miss_SellaneusCommented:
I think you need a GROUP BY 1 at the end of that.
PortletPaulEE Topic AdvisorCommented:
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

Open 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?
Éric MoreauSenior .Net ConsultantCommented:
>>It says Incorrect syntax near the keyword 'FROM'.

Your FROM needs to be right after the SELECT clause (before the WHERE clause).
Protecting & Securing Your Critical Data

Considering 93 percent of companies file for bankruptcy within 12 months of a disaster that blocked access to their data for 10 days or more, planning for the worst is just smart business. Learn how Acronis Backup integrates security at every stage

PortletPaulEE Topic AdvisorCommented:
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
Anthony PerkinsCommented:
Also, this condition:
AND dep2.ICU_DEPT_YN IS NOT NULL

Is redundant.
Becky EdwardsEpic Clarity DeveloperAuthor Commented:
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.
Éric MoreauSenior .Net ConsultantCommented:
can you give us a sample of your data (in Excel format maybe) and the according wanted results?
Becky EdwardsEpic Clarity DeveloperAuthor Commented:
yes, here is the spreadsheet.
Clabsi_Count.xls
Éric MoreauSenior .Net ConsultantCommented:
could it be as simple as:

select count(*) from (select distinct PAT_ENC_CSN_ID FROM F_IP_HSP_PAT_DAYS) as A

Open in new window

Experts Exchange Solution brought to you by

Your issues matter to us.

Facing a tech roadblock? Get the help and guidance you need from experienced professionals who care. Ask your question anytime, anywhere, with no hassle.

Start your 7-day free trial
Becky EdwardsEpic Clarity DeveloperAuthor Commented:
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.
Éric MoreauSenior .Net ConsultantCommented:
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

Open in new window

Becky EdwardsEpic Clarity DeveloperAuthor Commented:
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.....
It's more than this solution.Get answers and train to solve all your tech problems - anytime, anywhere.Try it for free Edge Out The Competitionfor your dream job with proven skills and certifications.Get started today Stand Outas the employee with proven skills.Start learning today for free Move Your Career Forwardwith certification training in the latest technologies.Start your trial today
Microsoft SQL Server 2008

From novice to tech pro — start learning today.