CODEWITHSUNDEEP
sql
Joshua Gross
Joshua Gross

Reputation: 1

SQL - Count every possible combination

I have a table with diagnoses and patientids. The table has one row per diagnosis with a patient id and a diagnosis.

eg

Patient ID | Diagnosis
-----------|----------
0          | Asthma
1          | Hypertension
1          | Asthma
2          | Asthma
2          | Hypertension
2          | Cancer
3          | Asthma

And I'd like to output something along the lines of

Asthma                          | 4
Hypertension                    | 2
Asthma & Hypertension           | 2
Asthma, Hypertension and Cancer | 1

How can I count the number of diagnoses combinations with the names of the Diagnosis?

i.e. x number of patients have Asthma, x number of patients have asthma and hypertension, x number of patients have diabetes, heart disease, lung disease, cancer, etc.

Some patients have as many as 12 diagnoses. Thank you in advance!

Upvotes: 0

Views: 285

Answers (2)

user330315
user330315

Reputation:

If I understand you correctly, you can do this with a recursive query:

with recursive all_diags as (
   select patient_id, diagnosis, diagnosis as diagnosis_list
   from diagnostics
   union all 
   select c.patient_id, c.diagnosis, p.diagnosis_list||','||c.diagnosis
   from diagnostics c
     join all_diags p on p.patient_id = c.patient_id and p.diagnosis < c.diagnosis
)
select diagnosis_list, count(*)
from all_diags
group by diagnosis_list
order by diagnosis_list;

Note that the keyword recursive is required by the SQL standard but not all DBMS actually support using that keyword.

There is a difference to your expected output though: my solution also returns the combinations: Cancer, Cancer,Hypertension and Asthma,Cancer which you didn't include in your sample output.

Here is a SQLFiddle example: http://sqlfiddle.com/#!15/21087/1

Upvotes: 0

Sam CD
Sam CD

Reputation: 2097

This might require editing from someone more well versed than myself in using FOR XML to concatenate rows, but this is one way to do it:

WITH CTE AS (
SELECT
PatientID,
STUFF(
    (
    SELECT ', ' + [Diagnosis] 
    FROM Table 
    WHERE (PatientID = B.PatientID) 
    ORDER BY Diagnosis
    FOR XML PATH('')
    )
    ,1,1,'') AS Diagnoses
FROM Table B
  )

SELECT Diagnoses,COUNT(DISTINCT PatientID) as Total
FROM CTE
GROUP BY Diagnoses

Basically, you are creating a concatenated value for all diagnoses for each patient, after ordering by Diagnosis (so that i.e. 'Condition1,Condition2' is not read differently from 'Condition2,Condition1'), then creating a second query to get the count for each combination

Upvotes: 1

Related Questions