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
select ICD9_DGNS_CD_2, 'CD_2' as Source_Column, count(1) as frequency from seis735_db.ip group by ICD9_DGNS_CD_2
select ICD9_DGNS_CD_3, 'CD_3' as Source_Column, count(1) as frequency from seis735_db.ip group by ICD9_DGNS_CD_3
select ICD9_DGNS_CD_4, 'CD_4' as Source_Column, count(1) as frequency from seis735_db.ip group by ICD9_DGNS_CD_4
select ICD9_DGNS_CD_5, 'CD_5' as Source_Column, count(1) as frequency from seis735_db.ip group by ICD9_DGNS_CD_5
select ICD9_DGNS_CD_6, 'CD_6' as Source_Column, count(1) as frequency from seis735_db.ip group by ICD9_DGNS_CD_6
select ICD9_DGNS_CD_7, 'CD_7' as Source_Column, count(1) as frequency from seis735_db.ip group by ICD9_DGNS_CD_7
select ICD9_DGNS_CD_8, 'CD_8' as Source_Column, count(1) as frequency from seis735_db.ip group by ICD9_DGNS_CD_8
select ICD9_DGNS_CD_9, 'CD_9' as Source_Column, count(1) as frequency from seis735_db.ip group by ICD9_DGNS_CD_9
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
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
select ICD9_DGNS_CD_2 from seis735_db.ip
select ICD9_DGNS_CD_3 from seis735_db.ip
select ICD9_DGNS_CD_4 from seis735_db.ip
select ICD9_DGNS_CD_5 from seis735_db.ip
select ICD9_DGNS_CD_6 from seis735_db.ip
select ICD9_DGNS_CD_7 from seis735_db.ip
select ICD9_DGNS_CD_8 frequency from seis735_db.ip
select ICD9_DGNS_CD_9 frequency from seis735_db.ip
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).
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
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