How to SUM multiple fields with condition-CodePudding

Here is my table structure

id  col1  col2  col3  col4
1       1     1     2     1

the thing is, i want to SUM field (col1,col2,col3,col4) but only the value 1. so based on the record above. the result would be 3 (col1 col2 col4), becauser value from col3 is 2 not 1.

sorry for bad grammar.

CodePudding user response：

If i undestood it correctly, this should do what you want (note that you have to change the column_name and table_name to make this work properly, since you didn't posted the names):

SELECT SUM(column_name)
FROM table_name
WHERE column_name = 1;

CodePudding user response：

Use a case expression to only sum on a value of 1.

select sum(case when col1 = 1 then col1 else 0 end)
      sum(case when col2 = 1 then col2 else 0 end)
      sum(case when col3 = 1 then col3 else 0 end)
      sum(case when col4 = 1 then col4 else 0 end)
from table

You can do these in a single sum() by surrounding the cases with parentheses as well, but this reads better in my opinion.

CodePudding user response：

SELECT
    SUM(
        CASE WHEN Col1 = 1 THEN Col1  ELSE 0 END
          CASE WHEN Col2 = 1 THEN Col2 ELSE 0 END
          CASE WHEN Col3 = 1 THEN Col3 ELSE 0 END
          CASE WHEN Col4 = 1 THEN Col4 ELSE 0 END
        ) as RowTotal
FROM
    [MyTable]

CodePudding user response：

Either you mean:

You want to calculate a new column on each row which is the sum of columns 1 through 4 if those columns are 1 (which is the same as the count of columns which are 1), or
You want to calculate the sum of those columns but only when the row values of those columns are 1

For 1:

SELECT id, col1, col2, col3, col4
, CASE WHEN col1 = 1 THEN 1 ELSE 0 END
    CASE WHEN col2 = 1 THEN 1 ELSE 0 END
    CASE WHEN col3 = 1 THEN 1 ELSE 0 END
    CASE WHEN col4 = 1 THEN 1 ELSE 0 END
AS row_count_of_ones
FROM table_name;

For 2:

SELECT SUM(
    CASE WHEN col1 = 1 THEN 1 ELSE 0 END
    CASE WHEN col2 = 1 THEN 1 ELSE 0 END
    CASE WHEN col3 = 1 THEN 1 ELSE 0 END
    CASE WHEN col4 = 1 THEN 1 ELSE 0 END
     ) AS count_of_ones
FROM table_name;