sqlmysqlmariadbheidisql

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

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

enter image description here

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?

Solution

  • 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.