只有当所有列值不为0时，才对行求和

I have this table with five columns (a, b, c, d, e) and I need to sum the values of a column but only if every column's value on that specific row is not 0.

这个表格有五列(a, b, c, d, e)我需要对一列的值求和，但前提是这一行上的每一列的值都不是0。

Lets say the table I have is like this:

假设我的桌子是这样的:

A | B | C | D | E 
––––––––––––––––––
1 | 0 | 2 | 1 | 2

1 | 1 | 1 | 2 | 1

1 | 3 | 4 | 5 | 2

In this case, I only have to sum the values of row 2 and 3, since in row 1 there's a 0, but I am not really sure how to go about it

在这种情况下，我只需要把第2行和第3行的值相加，因为第1行有一个0，但是我不知道该怎么做

3 个解决方案

#1

Try this

试试这个

SELECT
    SUM(
       CASE WHEN A > 0 THEN A END
       +
       CASE WHEN B > 0 THEN B END
       +
       CASE WHEN C > 0 THEN C END
       +
       CASE WHEN D > 0 THEN D END
       +
       CASE WHEN E > 0 THEN E END
       )
       FROM YourTable

#2

You can achieve this by using not in:

你可以使用not in:

select sum(a),sum(b), sum(c), sum(d), sum(e) 
from tbl 
where 0 not in (a, b, c, d, e)

Demonstration

示范

#3

Perhaps...

也许……

WITH VTE AS (
    SELECT *
    FROM (VALUES (1,0,2,1,2),
                 (1,1,1,2,1),
                 (1,3,4,5,2)) V(a,b,c,d,e))
SELECT CASE WHEN 0 NOT IN(a,b,c,d,e) THEN a+b+c+d+e END AS SumValue
FROM VTE;

The OP doesn't state what that want returned in the event a row does have a 0, so returned I returned NULL.

OP没有说明在行有0的情况下需要返回什么，因此返回的是NULL。

#1