SUM with multiple conditions and one null

Transact-SQL
1

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.

2

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
3

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
4

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)?

5 
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
2 Likes
6

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