sql - sql获取多列的总价值

Question

我有一个带有 10 列的表的 sql server 2012 db

name test1, test2, test3, test4,....
 bob  yes    no    null    yes
 john  no    yes    yes    null

我想从“测试”字段中获得所有结果的总数，所以我希望我的结果是

yes = 4
no = 2
null = 2

我曾尝试使用 cte、sum、case when 但我无法得到我正在寻找的结果。如果有人能告诉我如何获得我正在寻找的结果，下面是我的 sql 示例。

       with cte as 
  (
SELECT
test1,
sum (case when test1 = 'yes' then 1 else 0 end) as yes,
sum (case when test1= 'no' then 1 else 0 end) as no,
sum (case when test1 is null then 1 else 0 end) as notset
from names
group by Inspection1Type)
  select 
   sum (yes) as 'yes',
    sum(no) as 'no'
     sum(notset) as 'Not Set'
  from cte;

它适用于第一列，但不适用于其余列，因为我正在寻找相同的值它抱怨我的别名相同

标签： sqlsql-serversumcommon-table-expressioncase-when

Answer 1

试试这种剪切和粘贴方法：

SELECT
    sum (case when test1 = 'yes' then 1 else 0 end
        +case when test2 = 'yes' then 1 else 0 end
        +case when test3 = 'yes' then 1 else 0 end
        ...
        +case when test10 = 'yes' then 1 else 0 end) as yes,
    sum (case when test1 = 'no' then 1 else 0 end
        +case when test2 = 'no' then 1 else 0 end
        +case when test3 = 'no' then 1 else 0 end
        ...
        +case when test10 = 'no' then 1 else 0 end) as no,
    sum (case when test1 is null then 1 else 0 end
        +case when test2 is null then 1 else 0 end
        +case when test3 is null then 1 else 0 end
        ...
        +case when test10 is null then 1 else 0 end) as notset
from names