## sql/distinct/count

Solved
Super Contributor
Posts: 464

# sql/distinct/count

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

Accepted Solutions
Solution
‎05-02-2013 11:07 PM
Posts: 5,521

## Re: sql/distinct/count

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

PG

All Replies
Solution
‎05-02-2013 11:07 PM
Posts: 5,521

## Re: sql/distinct/count

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

PG
Super Contributor
Posts: 464

## Re: sql/distinct/count

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?

🔒 This topic is solved and locked.