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.

mike01
March 23, 2020, 7:38pm
#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

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
```

2 Likes

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