469,950 Members | 2,328 Online
Bytes | Developer Community
New Post

Home Posts Topics Members FAQ

Post your question to a community of 469,950 developers. It's quick & easy.

Need SQL help - Select Distinct, Count, Group By

Hello all,

I'm having some trouble setting up a query.

Background: The table TBLSCREEN stores data about screenings of
patients/subjects for eligibility to participate in a health study. A
subject can be screened multiple times.

The simplified table structure is

SITE - location of subject
SUBJECTID - ID of subject
SCREENNUM - the number of the screening (1 represents first screening,
etc)
SCREENDATE - the date the subject was screened

A subject is uniquely identified by the combination of SITE and
SUBJECTID. A screening is uniquely identified by the combination of
SITE, SUBJECTID, and SCREENNUM.

I need to create a query that shows the number of unique subjects
screened per month.

If a subject is screened twice in two different months, only the first
screening should count.

I've tried variations of something like this, but nothing has worked
so far:

SELECT COUNT(*) AS NumSubj FROM
(SELECT DISTINCT SITE, SUBJECTID
FROM TBLSCREEN)
GROUP BY Format(SCREENDATE,"yyyy-mm");

Thanks in advance for any help you may be able to provide,
Chris
Nov 13 '05 #1
2 9291
"Chris" <ch*****@mailinator.com> wrote in message
news:a0**************************@posting.google.c om...

I need to create a query that shows the number of unique subjects
screened per month.

If a subject is screened twice in two different months, only the first
screening should count.


When you work with groupings of temporal data it's often better to build and
populate a calendar table to hold the individual date groups, and then join
to this table in your query. I don't have time now but see if this puts you
on the right track, (do a search in google for calendar table) - if you
can't solve it post back and i'll write the sql.
Nov 13 '05 #2
> A subject is uniquely identified by the combination of SITE and
SUBJECTID. A screening is uniquely identified by the combination of
SITE, SUBJECTID, and SCREENNUM.

SELECT COUNT(*) AS NumSubj FROM
(SELECT DISTINCT SITE, SUBJECTID
FROM TBLSCREEN)
GROUP BY Format(SCREENDATE,"yyyy-mm");

Is this what you are looking for?

SELECT COUNT(*) AS NumSubj, site
FROM
(SELECT COUNT(subjectid), site, screennum
FROM site
GROUP BY site, screennum) as subquery
GROUP BY site
Nov 13 '05 #3

This discussion thread is closed

Replies have been disabled for this discussion.

Similar topics

4 posts views Thread by soni29 | last post: by
9 posts views Thread by Kelvin | last post: by
1 post views Thread by mianiro | last post: by
21 posts views Thread by tizmagik | last post: by
By using this site, you agree to our Privacy Policy and Terms of Use.