CODEWITHSUNDEEP
sqlnullsumaggregate-functions
Sekhar
Sekhar

Reputation: 5787

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 'VALID' column?

Any ideas? Thanks a lot!

Upvotes: 8

Views: 13713

Answers (3)

spencer7593
spencer7593

Reputation: 108410

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.

Upvotes: 22

Chris Shain
Chris Shain

Reputation: 51339

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

Upvotes: -1

SLaks
SLaks

Reputation: 887459

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

Upvotes: -1

Related Questions