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 UNION
s) 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.
top 10
in each column? Ortop 10
across all columns?postgresql
,oracle
,sql-server
,db2
, ...