Get a count of times distinct values occur in each column separately

Question

Here is what the source table looks like:

╔══════╦══════╦══════╗ ║ COL1 ║ COL2 ║ COL3 ║ ╠══════╬══════╬══════╣ ║ A ║ A ║ A ║ ║ A ║ A ║ B ║ ║ A ║ B ║ C ║ ║ B ║ B ║ C ║ ║ B ║ C ║ C ║ ║ C ║ C ║ C ║ ╚══════╩══════╩══════╝

I am looking to end up with results like this:

╔════════╦══════╦══════╦══════╗ ║ VALUES ║ COL1 ║ COL2 ║ COL3 ║ ╠════════╬══════╬══════╬══════╣ ║ A ║ 3 ║ 2 ║ 1 ║ ║ B ║ 2 ║ 2 ║ 1 ║ ║ C ║ 1 ║ 2 ║ 4 ║ ╚════════╩══════╩══════╩══════╝

I know this can be done unions, but my table has a large number of columns so I was hoping to find a more elegant solution.

could there be 0s or does every value appear in every column at least once? — FuzzyTree
– FuzzyTree, Commented Oct 2, 2014 at 12:55
There could be nulls but they should be grouped and counted as well. — Brian Badge
– Brian Badge, Commented Oct 2, 2014 at 13:12

FuzzyTree · Accepted Answer · 2014-10-02 13:31:29Z

If all values appear in the first column, you can get the counts for the first column with a simple group by and use a cross join and conditional aggregation to get the counts for the other columns

select t1.myvalues, t1.col1, sum(case when t2.col2 = t1.myvalues then 1 else 0 end) col2, sum(case when t2.col3 = t1.myvalues then 1 else 0 end) col3 from ( select col1 myvalues, count(*) col1 from Table1 group by col1 ) t1 cross join Table1 t2 group by t1.myvalues, t1.col1

http://sqlfiddle.com/#!4/5b35b/1

I used this method with slight modification to handle nulls which happen to not occur in the first column. Thank you!

redsoxlost · Accepted Answer · 2014-10-03 16:48:00Z

select 'A' as col, sum(decode(col1,'A',1,0)) as col1, sum(decode(col2,'A',1,0)) as col2, sum(decode(col3,'A',1,0)) as col3 from test_t

union

select 'B' as col, sum(decode(col1,'B',1,0)) as col1, sum(decode(col2,'B',1,0)) as col2, sum(decode(col3,'B',1,0)) as col3 from test_t

union

select 'C' as col, sum(decode(col1,'C',1,0)) as col1, sum(decode(col2,'C',1,0)) as col2, sum(decode(col3,'C',1,0)) as col3 from test_t

Collectives™ on Stack Overflow

Get a count of times distinct values occur in each column separately

2 Answers 2

1 Comment

Comments

Hot Network Questions

Collectives™ on Stack Overflow

2 Answers 2

1 Comment

Comments

Related