Can I join multiple column in a table?

Question

I am working school homework question. I have an inpatient table, I have to determine the top 10 ICD code that have the highest frequency. Please output each code with its frequency (i.e. occurrence) in the descending order. My SQL code is:

select ICD9_DGNS_CD_1, ICD9_DGNS_CD_2, ICD9_DGNS_CD_3, ICD9_DGNS_CD_4,ICD9_DGNS_CD_5,ICD9_DGNS_CD_6,
ICD9_DGNS_CD_7,ICD9_DGNS_CD_8,ICD9_DGNS_CD_9,ICD9_DGNS_CD_10, count(*) AS frequency
from seis735_db.ip
group by ICD9_DGNS_CD_1,ICD9_DGNS_CD_2,ICD9_DGNS_CD_3,ICD9_DGNS_CD_5,ICD9_DGNS_CD_6,ICD9_DGNS_CD_7,
ICD9_DGNS_CD_8,ICD9_DGNS_CD_9,ICD9_DGNS_CD_10
order by count(*) desc
LIMIT 10;

This outputs the top 10 in each ICD code diagnoses. I am trying to find just the top 10 from all the 10 columns? I am new to sql and I am having a hard time writing the query for this question.

Any help will be much appreciated. Thank you!

Answer 1

Following is one way to find the 10 most frequent values across all 10 columns - but only counting frequency for each value within its own column (explained further, later):

select AggregatedCounts.ICD_Value, AggregatedCounts.Source_Column, AggregatedCounts.frequency from
(
    select ICD9_DGNS_CD_1 as ICD_Value, 'CD_1' as Source_Column, count(1) as Frequency from seis735_db.ip group by ICD9_DGNS_CD_1  
    union
    select ICD9_DGNS_CD_2, 'CD_2' as Source_Column, count(1) as frequency from seis735_db.ip group by ICD9_DGNS_CD_2   
    union
    select ICD9_DGNS_CD_3, 'CD_3' as Source_Column, count(1) as frequency from seis735_db.ip group by ICD9_DGNS_CD_3   
    union
    select ICD9_DGNS_CD_4, 'CD_4' as Source_Column, count(1) as frequency from seis735_db.ip group by ICD9_DGNS_CD_4   
    union
    select ICD9_DGNS_CD_5, 'CD_5' as Source_Column, count(1) as frequency from seis735_db.ip group by ICD9_DGNS_CD_5   
    union
    select ICD9_DGNS_CD_6, 'CD_6' as Source_Column, count(1) as frequency from seis735_db.ip group by ICD9_DGNS_CD_6   
    union
    select ICD9_DGNS_CD_7, 'CD_7' as Source_Column, count(1) as frequency from seis735_db.ip group by ICD9_DGNS_CD_7   
    union
    select ICD9_DGNS_CD_8, 'CD_8' as Source_Column, count(1) as frequency from seis735_db.ip group by ICD9_DGNS_CD_8   
    union
    select ICD9_DGNS_CD_9, 'CD_9' as Source_Column, count(1) as frequency from seis735_db.ip group by ICD9_DGNS_CD_9   
    union
    select ICD9_DGNS_CD_10, 'CD_10' as Source_Column, count(1) as frequency from seis735_db.ip group by ICD9_DGNS_CD_10   
) as AggregatedCounts
order by frequency desc limit 10;

How this works:

The main part of this solution is that we have 10 separate SELECT statements.

The first selects all values from column 1 (ICD9_DGNS_CD_1), groups by these values and counts the frequency.

The next statement does the same for the second column, and so on.

By using the UNION operator, we merge together the results from all of these 10 SELECT statements - producing one big set of results. This counts the frequency of each unique value in each of the 10 columns and puts them all into a single long list.

The next part of the solution is that we SELECT from this result set. So the result of those 10 SELECT statements (with UNIONs) is named 'AggregatedCounts' - and we now select from these aggregated counts.

Note, in each of the 10 SELECT statements we are actually selecting 3 values: first the value in the column we are looking at, then a hard-coded string which tells you the name of the column we are looking at (like 'CD_1'), then finally the count. In the first SELECT statement we name these 3 columns (ie. we give each an alias): ICD_Value, Source_Column and Frequency.

Finally, in our outer select, we select these 3 column names. That outer select is ordered by Frequency, descending, and we limit the results to the top 10 rows - that is, the 10 rows with the highest value in the Frequency column - and we report what the value is, and from which column it originally came.

But note (!) If you have, say, the value 'aaa' in column 1, and 'aaa' in column 2 - they will not be counted together using this solution.

If we want to count values across all columns, then we use the following solution instead:

select AggregatedCounts.ICD_Value, count(1) as Frequency from
(
    select ICD9_DGNS_CD_1 as ICD_Value from seis735_db.ip
    union all
    select ICD9_DGNS_CD_2 from seis735_db.ip 
    union all
    select ICD9_DGNS_CD_3 from seis735_db.ip 
    union all
    select ICD9_DGNS_CD_4 from seis735_db.ip 
    union all
    select ICD9_DGNS_CD_5 from seis735_db.ip 
    union all
    select ICD9_DGNS_CD_6 from seis735_db.ip 
    union all
    select ICD9_DGNS_CD_7 from seis735_db.ip 
    union all
    select ICD9_DGNS_CD_8 frequency from seis735_db.ip 
    union all
    select ICD9_DGNS_CD_9 frequency from seis735_db.ip 
    union all
    select ICD9_DGNS_CD_10 frequency from seis735_db.ip  
) as AggregatedCounts
group by AggregatedCounts.ICD_Value
order by Frequency desc limit 10;

What have we changed here?

Firstly, the 10 selects are now just selecting all values - no group by, and no other values (like source column or counting).

Secondly, the UNION statements have been changed to UNION ALL - this is because by default, UNION will include only unique values in the result set. This time we want to do our counting in the outer SELECT, so we need all instances of all values in our AggregatedCounts list.

Finally, as just suggested, now our GROUP BY has moved to the outer SELECT statement - and we are doing our counting here - across all values from all columns.

You can view the first solution working on db-fiddle

And you can view the second solution working on db-fiddle - using exactly the same source data.

Note on these 2 fiddle solutions, in the first solution, the value '500' ranks above the value '401' (with frequencies 30 and 28) but in the second, '401' ranks above '500' (with frequencies 31 and 30). Examine the last 3 lines of inserting data - I have put the value '401' into the 5th column here - so '401' appears 3 times in column 5, and 28 times in column 4 = 31 times in total. This explains the difference in results, and the difference between the two SELECT statements.

Lastly, using UNION like this is making your database do a lot of work. If your table is large, this solution may take a long time to run and slow down your database. There may be other ways to solve this using procedures, but this is an easy to understand approach using SELECT statements.