using the min() function inside of the case statement
810378Nov 3 2010 — edited Nov 3 2010I'm having trouble with the following select statment
select
patient_id
, sum(case when ch.cancer_site_id in ('3', '39', '12') and min(ch.diagnosis_age) < 40 then '1' else '0' end) as Breast
from ...
group by patient_id
I'm getting a "not a single-group group function" error.
The main table I'm working with is set up thusly.
PatientID| diagnosis_age|cancer_site_id
12345678| 35| 4
12345678|40|4
12345678|42|12
12345678|34|31
87654321|50|12
cancer_site_ids of 4 and 31 need to get grouped together and called breast. cancer_site_id of 12 (and others) is colon cancer.
Eventually I need the output to look like this
PatientID| min(breast_diagnosis_age) < 40 | min(breast_diagnosis_age) > 40 | min(colon_diagnosis_age) > 40
-------------------------------------------------------------------------------
12345678 | 1 | 0 |1
87654321| 0 | 0 | 1
My final query is a lot more complicated than this, but this is the last part I can't figure out.