Finding the most common value in sql and ordering from highest to lowest (asc)

Question

I have a simple request.

select id, patient_id, diagnosis from dbo.diabetes_rx_tracker group by id, patient_id, diagnosis 

the most common diagnostic codes should appear at the top of the list with a score.

I tried using the count function.

but it returns all values ​​for each patient as 1 instead of adding.

 select id, patient_id, count(diagnosis) from dbo.diabetes_rx_tracker group by id, patient_id, diagnosis 

I also continued to throw errors while editing my expression group.

Any help would be greatly appreciated. Thanks.

Answer 1

It looks like you're not aggregated enough. Each field in your GROUP represents a field for aggregation in conjunction with others. Thus, it will only show you the diagnosis by id, by patient, by diagnosis. Not very helpful.

To get the most common diagnosis, remove id and patient_id from the group:

 select count(diagnosis) from dbo.diabetes_rx_tracker group by diagnosis order by count(diagnosis) desc 

To get the most common codes for all identifiers, remove patient_id from the group and select:

 select diagnosis, id, count(1) as CodeCount from dbo.diabetes_rx_tracker group by diagnosis, id order by count(diagnosis) desc 

UPDATE: Based on your new criteria, this is what you want.

 select id, diagnosis, ct from ( select row_number() OVER (PARTITION BY id ORDER BY count(diagnosis) desc ) as rn, count(diagnosis) as ct, diagnosis, id from dbo.diabetes_rx_tracker group by id, diagnosis ) ranked where rn = 1 order by CT desc