By using this site, you agree to our updated Privacy Policy and our Terms of Use. Manage your Cookies Settings.
459,480 Members | 1,229 Online
Bytes IT Community
+ Ask a Question
Need help? Post your question and get tips & solutions from a community of 459,480 IT Pros & Developers. It's quick & easy.

Need SQL help - Select Distinct, Count, Group By

P: n/a
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
Share this Question
Share on Google+
2 Replies


P: n/a
"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

P: n/a
> 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.