count distinct within a query - SQL Server Forums

Please start any new threads on our new site at https://forums.sqlteam.com. We've got lots of great SQL Server experts to answer whatever question you can come up with.

All Forums

Other Forums

MS Access

count distinct within a query

Author

Topic

pyrrhus_finch
Yak Posting Veteran

51 Posts

Posted - 2004-10-28 : 18:42:05

My query looks like this and gives me a nice table
people grouped the month attended.

SELECT Year([ATTEND_DATE]) AS [YEAR], Format(DateSerial(2000,Month([ATTEND_DATE]),1),"mmm") AS [MONTH], Count([PrinDX].[ENCOUNTER_NUMBER]) AS PERSONS_ATTENDING
FROM C_PrinDX
GROUP BY Year([ATTEND_DATE]), Month([ATTEND_DATE]);

The problem is that some attend more than once in a month or have multiple records on the same day.

I want to get the count of Unique visits per month:

SELECT Year([ATTEND_DATE]) AS [YEAR], Format(DateSerial(2000,Month([ATTEND_DATE]),1),"mmm") AS [MONTH], COUNT DISINCT [PrinDX].[ENCOUNTER_NUMBER]) AS PERSONS_ATTENDING
FROM C_PrinDX
GROUP BY Year([ATTEND_DATE]), Month([ATTEND_DATE]);

But it wont work.
You I can't use DISTINCT in this manner? Or is my syntax incorrect?
I tried other variations with no luck.

Any Ideas?
Thank you so much.

nr
SQLTeam MVY

12543 Posts

Posted - 2004-10-28 : 18:49:39

COUNT(DISTINCT PrinDX.ENCOUNTER_NUMBER)

==========================================
Cursors are useful if you don't know sql.
DTS can be used in a similar way.
Beer is not cold and it isn't fizzy.

pyrrhus_finch
Yak Posting Veteran

51 Posts

Posted - 2004-10-28 : 19:15:20

Thanks, tried that:

SELECT Year([ATTEND_DATE]) AS [YEAR], Format(DateSerial(2000,Month([ATTEND_DATE]),1),"mmm") AS [MONTH], COUNT (DISTINCT [PrinDX].[ENCOUNTER_NUMBER]) AS PERSONS_ATTENDING
FROM C_PrinDX
GROUP BY Year([ATTEND_DATE]), Month([ATTEND_DATE]);

GIVES ME THE: Syntax error (missing operator)in query expression 'COUNT (DISTINCT [PrinDX].[ENCOUNTER_NUMBER])'

pyrrhus_finch
Yak Posting Veteran

51 Posts

Posted - 2004-10-28 : 19:58:45

Anyone? Have another idea? *pulling my hair out*

nr
SQLTeam MVY

12543 Posts

Posted - 2004-10-28 : 20:52:49

Oops - didn't notice this was access.
count(distint fld) isn't supported

==========================================
Cursors are useful if you don't know sql.
DTS can be used in a similar way.
Beer is not cold and it isn't fizzy.

jsmith8858
Dr. Cross Join

7423 Posts

Posted - 2004-10-28 : 22:03:31

you need two queries:

query 1 SELECTs from your table, and groups by Year,Month,Encounter_Number (or SELECT DISTINCT)

query 2 SELECTs from query 1, grouping by Year,Month, returning the COUNT(*)

that is the equivalent of COUNT(Distinct) in this case.

- Jeff

pyrrhus_finch
Yak Posting Veteran

51 Posts

Posted - 2004-10-29 : 12:30:52

thanks guys. access stinks. i keep thinking i'm using mssql too. really frustrating.

pyrrhus_finch
Yak Posting Veteran

51 Posts

Posted - 2004-10-29 : 13:27:42

Like this? Works except it doesn't. It gives me only one per attend date as well as encounter number.
Any more ideas?

SELECT Year(ATTEND_DATE) AS YEAR, Format(DateSerial(2000,Month(ATTEND_DATE),1),"mmm") AS MONTH, COUNT (DISTINCT ENCOUNTER_NUMBER) AS PERSONS_ATTENDING
FROM (SELECT DISTINCT ENCOUNTER_NUMBER, ATTEND_DATE FROM C_PrinDX ) AS BLUBBER
GROUP BY Year(ATTEND_DATE), Month(ATTEND_DATE);

jsmith8858
Dr. Cross Join

7423 Posts

Posted - 2004-10-29 : 13:43:06

quote:
Originally posted by pyrrhus_finch

Like this? Works except it doesn't. It gives me only one per attend date as well as encounter number.
Any more ideas?

SELECT Year(ATTEND_DATE) AS YEAR, Format(DateSerial(2000,Month(ATTEND_DATE),1),"mmm") AS MONTH, COUNT (DISTINCT ENCOUNTER_NUMBER) AS PERSONS_ATTENDING
FROM (SELECT DISTINCT ENCOUNTER_NUMBER, ATTEND_DATE FROM C_PrinDX ) AS BLUBBER
GROUP BY Year(ATTEND_DATE), Month(ATTEND_DATE);

NO. As I said, you need to group the first query by Year,Month:


query1:

SELECT 
   Year(Attend_Date) as Yr, Month(Attend_Date) as Mnth,      
   ENCOUNTER_NUMBER, 
FROM 
   C_PrinDX 
GROUP BY 
  Year(Attend_Date), Month(Attend_Date)


Final Result:

SELECT 
  Yr, Mnth, COUNT(*) as Total
FROM 
  Query1
GROUP BY 
  Yr, Mnth

- Jeff

pyrrhus_finch
Yak Posting Veteran

51 Posts

Posted - 2004-10-29 : 17:45:07

Thanks Jeff.
I was confused because I couldn't call named queries in the sql editor - then I just fiqured out I CAN call a SAVED query in a select statement in Access.

Used to calling sprocs.
This will work. Thank you again.

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources