CONDITIONAL SUM T-SQL

Question

I have a table in this format

COL1 COL2 COL3
A    NULL  4
A    NULL  4
A    VAL1  4
A    VAL2  4
A    VAL3  4
B    NULL  5
B    VAL1  6

I need to output the output as follows:

COL1 TOTAL VALID
A    20     12
B    11     6

My question is: how do I get the "VALID" column - it should do the sum of col3 only if col2 is not null.

I was able to get the "TOTAL" field correctly using the SUM and group by clauses. But how to calculate the "VALID" column?

Any ideas? Many thanks!

Answer 1

Something like that:

SELECT col1
     , SUM(col3) AS TOTAL
     , SUM(CASE WHEN col2 IS NOT NULL THEN col3 ELSE NULL END) AS VALID
  FROM mytable
 GROUP BY col1

The “trick” is to use the CASE expression to conditionally return the value of col3.

Answer 2

You can use a subquery:

SELECT SUM(Col3) AS Total, 
       (SELECT SUM(Filtered.Col3) 
        FROM table Filtered 
        WHERE Filtered.Col1 = Grouped.Col1 
          AND Filtered.Col2 IS NOT NULL
       ) AS Valid
FROM table Grouped
GROUP BY Col1

Answer 3

This should work ...

SELECT B.COL1, SUM(A.COL3) AS TOTAL, SUM(B.COL3) AS VALID
FROM 
(SELECT COL1, COL3 FROM ORIGINAL WHERE COL2 IS NULL) A
INNER JOIN (SELECT COL1, COL3 FROM ORIGINAL) B ON A.COL1 = B.COL1