Sum of a column based on multiple conditions returns null. Why is that?

Question

I have an expense table where multiple expenses can be tied to an ID, but each of these expenses can have a specific type.

I can get the sum of ONE type.

SELECT 
    SUM(expense) AS total 
FROM expenses
WHERE id = 1 AND type = 'cleaning';

But I would like to get the sum of an expense that has two types, so I tried

SELECT
    SUM(expense) AS total 
FROM expenses 
WHERE id = 1 
AND type = 'cleaning' 
AND type = 'painting';

I expected this to return 3000, but instead it returns NULL.

Why does the AND operator not work in this instance?

Answer 1

Your

SELECT
    SUM(expense) AS total 
FROM expenses 
WHERE id = 1 
AND type = 'cleaning' 
AND type = 'painting';

query says that you want to SUM the expense of all records whose Type equals cleaning and painting at the same time.

Since Type either does not match either of those values, or it matches exactly one of them, whereas your criteria expects it to match both, none of the records matches the criteria, so your SUM computes the sum of empty set.

Try:

SELECT
    SUM(expense) AS total 
FROM expenses 
WHERE id = 1 
AND type IN ('cleaning', 'painting');

or

SELECT
    SUM(CASE WHEN Type = 'cleaning' THEN expense ELSE 0 END) AS total_cleaning,
    SUM(CASE WHEN Type = 'painting' THEN expense ELSE 0 END) AS total_painting
FROM expenses 
WHERE id = 1;

instead.

Answer 2

A where clause is evaluated for each row. There is no row for which:

type = 'cleaning' AND type = 'painting'

Because the sum sees no rows, it returns null.

Answer 3

It is because according to the query, you are searching for a type that has both painting and cleaning.

You can rather try using OR for getting the sum of painting and cleaning.

SELECT SUM(expense) AS total FROM expenses WHERE id = 1 AND (type = 'cleaning' OR type = 'painting');