计算每个ID的值数 [英] Count number of values per id

问题描述

假设我有table1:

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

我有table2:

    id  
    1  
    2  
    3  

我想对每个id的每一列中的值数进行计数，以得到这样的输出. (例如ID 1具有2-z，一个y和一个x)

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    

会建议哪种方法?

我当前正在使用Oracle 12c

I am currently using Oracle 12c

推荐答案

执行GROUP BY，使用COUNT(仅计算非空值):

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

修改:

如果值不是null而是'.' (或其他)，请使用case表达式进行条件计数，例如:

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

这篇关于计算每个ID的值数的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！