CODEWITHSUNDEEP
sqloracle11g
Brian Badge
Brian Badge

Reputation: 45

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

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.

Upvotes: 2

Views: 50

Answers (2)

redsoxlost
redsoxlost

Reputation: 1235

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

Upvotes: 0

FuzzyTree
FuzzyTree

Reputation: 32402

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

Upvotes: 1

Related Questions