sql/distinct/count

[Tal]

hi guys,'

i have a table of patient-id,date,and tests and i need to write a SQL statement that would count the number of distinct tests per patient (distinct= on a different date) but i dont actually understand what they asking me for.

Do they mean:

for id=22,count(distinct(tests))=1

for id=23,cont(distinct(tests))=5 and so on...?

or maybe sum(distinct(tests))=20

and sum(distinct(tests))=(10+50+100+120+180) or maybe neither?

22 29/12/1999 20

22 29/12/1999 20

23 01/11/1999 10

23 01/12/1999 10

23 08/12/1999 10

23 19/04/2000 50

23 20/06/2000 100

23 15/08/2000 120

23 10/09/2000 180

23 11/9/2000 180

23 12/9/2000 180

24 01/8/2001 160

24 01/8/2001 160

24 15/8/2001 160

24 01/1/2002 160

25 1/6/1999 10

25 2/6/1999 10

25 3/6/1999 10

25 19/4/2000 50

25 20/6/2000 100

25 15/8/2000 120

25 31/10/2000 180

25 28/11/2000 80

25 29/11/2000 80

25 30/11/2000 80

25 15/08/2001 160

25 01/1/2001 190

[PGStats · Accepted solution]

My guess would be that given the variables (patientId, testDate testId), the required counts would be given by:

proc sql;

create table want as

select patientId, count(*) as nbTests

from(

     select unique patientId, testDate, testId

     from have)

group by patientId;

select * from want;

quit;

                                patientId   nbTests
                                -------------------
                                       22         1
                                       23         9
                                       24         3
                                       25        12

PG

[Tal]

yea it does make sense.distinct patientid,date,test.Thanks PG

and if i want to write a SQL statement that would count the number of individuals   who have the same testid value on at least 3 consecutive test dates. What would that be,any idea?