CONDITIONAL SUM T-SQL

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 bring out 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 sum of col3 only if col2 is not null.

I was able to get the 'TOTAL' field correctly using SUM and group by clauses. But how do I compute the开发者_JAVA技巧 'VALID' column?

Any ideas? Thanks a lot!

---

Something like this:

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.

---

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

---

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