finding the most common value in sql and ordering by highest to lowest (asc)

Question

I have a simple query.

    select id, patient_id, diagnosis from dbo.diabetes_rx_tracker

group by id, patient_id, diagnosis

the most common diagnosis codes need to appear at the top of the list with a count.

I tried using the count function.

but it returns all the 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 kept throwing errors when editing my group by statement.

any help would be greatly appreciated. Thank you.

Answer 1

This looks like you're not aggregating enough. Each field in your GROUP by represents a field for aggregation in total with the others. So this will only show you the diagnoses by id, by patient, by diagnosis. Not very helpful.

To get the most common diagnoses period, remove id and patient_id from the grouping as well:

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

To get the most common codes across all ids, remove patient_id from the grouping 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