CODEWITHSUNDEEP
sqloracle-databasecount
Nico
Nico

Reputation: 5

Count by column

I have the following data:

data

And I want to know how often each value exists in each column. So my preferred output should look like this:

output

I'd be really grateful, if anybody can help me. Thanks!

Upvotes: 0

Views: 81

Answers (3)

MT0
MT0

Reputation: 167822

Use UNPIVOT then PIVOT:

SQL Fiddle

Oracle 11g R2 Schema Setup:

CREATE TABLE table_name ( a, b, c, d ) AS
SELECT 1, 2, 1, 2 FROM DUAL UNION ALL
SELECT 1, 2, 2, 2 FROM DUAL UNION ALL
SELECT 2, 1, 3, 3 FROM DUAL UNION ALL
SELECT 3, 3, 2, 4 FROM DUAL UNION ALL
SELECT 4, 4, 2, 5 FROM DUAL UNION ALL
SELECT 5, 5, 5, 5 FROM DUAL;

Query 1:

SELECT *
FROM   table_name
UNPIVOT( value FOR name IN ( A, B, C, D ) )
PIVOT  ( COUNT(1) FOR name IN ( 'A' AS A, 'B' AS B, 'C' AS C, 'D' AS D ) )

Results:

| VALUE | A | B | C | D |
|-------|---|---|---|---|
|     1 | 2 | 1 | 1 | 0 |
|     2 | 1 | 2 | 3 | 2 |
|     4 | 1 | 1 | 0 | 1 |
|     5 | 1 | 1 | 1 | 2 |
|     3 | 1 | 1 | 1 | 1 |

Upvotes: 0

user5683823
user5683823

Reputation: 

with
     inputs ( a, b, c, d ) as (
       select 1, 2, 1, 2 from dual union all
       select 1, 2, 2, 2 from dual union all
       select 2, 1, 3, 3 from dual union all
       select 3, 3, 2, 4 from dual union all
       select 4, 4, 2, 5 from dual union all
       select 5, 5, 5, 5 from dual
     )
-- End of simulated inputs (for testing only, not part of the solution).
-- SQL query begins BELOW THIS LINE. Use your actual table and column names.
select grade,
       count(case when a = grade then 0 end) as a,
       count(case when b = grade then 0 end) as b,
       count(case when c = grade then 0 end) as c,
       count(case when d = grade then 0 end) as d
from   inputs cross join (select level as grade from dual connect by level <= 5)
group by grade
order by grade
;

     GRADE          A          B          C          D
---------- ---------- ---------- ---------- ----------
         1          2          1          1          0
         2          1          2          3          2
         3          1          1          1          1
         4          1          1          0          1
         5          1          1          1          2

NOTE: This is essentially the same as MT0's solution, but both the unpivoting and the pivoting are done the "old way" (as they were done before PIVOT and UNPIVOT operators were introduced in Oracle 11.1).

Upvotes: 1

Thorsten Kettner
Thorsten Kettner

Reputation: 94859

That's a weird table structure and/or task. You may want to think over your database design. Anyway ...

select 
  num, 
  coalesce(agga.cnt, 0) as a,
  coalesce(aggb.cnt, 0) as b,
  coalesce(aggc.cnt, 0) as c,
  coalesce(aggd.cnt, 0) as d
from            (select a as num, count(*) as cnt from mytable group by a) agga
full outer join (select b as num, count(*) as cnt from mytable group by b) aggb using(num)
full outer join (select c as num, count(*) as cnt from mytable group by c) aggc using(num)
full outer join (select d as num, count(*) as cnt from mytable group by d) aggd using(num)
order by num;

Upvotes: 0

Related Questions