CODEWITHSUNDEEP
sqloracle-database
pgr
pgr

Reputation: 125

Count number of values per id

Let's assume I have table1:

id  value1  value2 value3
1   z       null    null
1   z       null    null
1   null    y       null
1   null    null    x
2   null    y       null
2   z       null    null 
3   null    y       null
3   null    null    null 
3   z       null    null

and I have table2:

    id  
    1  
    2  
    3

I want to count number of values in each column per id to have output like this. (ex. id 1 has 2 - z's, one y and one x) 

 id value1 value2  value3  
  1   2     1      1     
  2   1      1     0   
  3   1      1     0

What approach would be suggested?

I am currently using Oracle 12c

Upvotes: 1

Views: 6645

Answers (1)

jarlh
jarlh

Reputation: 44796

Do a GROUP BY, use COUNT (which only counts non-null values):

select id,
       count(value1) as value1,
       count(value2) as value2,
       count(value3) as value3
from table1
group by id

Edit:

If values are not null but '.' (or something else), do use case expressions to do conditional counting, something like:

select id,
       count(case when value1 <> '.' then 1 end) as value1,
       count(case when value2 <> '.' then 1 end) as value2,
       count(case when value3 <> '.' then 1 end) as value3
from table1
group by id

Upvotes: 7

Related Questions