SQLTeam.com | Weblogs | Forums

SUM with multiple conditions and one null

Fields
Asked_Amount
Partial_Amount
State

So, I would like to retrieve:

  • (one column) sum all the rowsvalues of Asked_Amount IF State='1' AND Partial_Amount IS Null**

Tested to create a column with this statement and returned error. But If I remove the second condition it works.

CASE WHEN State = '1' AND [Partial_Amount] = 'IS NULL' THEN [Asked_Amount] ELSE 0 END

  • (one column) sum all Partial_Amount

Tested and works:

SUM([Partial_Amount]) AS Total_Partial_Amount

  • (one column) the sum of these two columns

Haven't tested anything yet since I couldn't even solve the 1st one.

What am I doing wrong? Thanks in advance.

you have the right query, just put sum around it

sum(CASE WHEN State = '1' AND [Partial_Amount] = 'IS NULL' THEN [Asked_Amount] ELSE 0 END)

1 Like

IS NULL is its own condition, it's not part of an =:

SUM(CASE WHEN State = '1' AND [Partial_Amount] IS NULL THEN [Asked_Amount] ELSE 0 END) AS Total_Partial_Amount

3 Likes

Thank you all for your quick responses. Yes, I forgot the SUM and yes there shouldn't be a = before IS NULL!

How can I now sum these 2 sums? (the case example column with the Total_Partial_Amount)?

SELECT
    SUM(CASE WHEN State = '1' AND [Partial_Amount] IS NULL THEN [Asked_Amount] ELSE 0 END) AS Total_Asked_Amount,
    SUM([Partial_Amount]) AS Total_Partial_Amount,
    SUM(CASE WHEN State = '1' AND [Partial_Amount] IS NULL THEN [Asked_Amount] ELSE 0 END) + 
    SUM([Partial_Amount]) AS Total_Amount
1 Like

I had tried that but I must have miss something because it was returning error! Thank you very much