Mysql Question posted in
The official MySQL documentation can be found here.

Mysql – 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?

Tags:

3

Answers


  1. 0

    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.

    Login or Signup to reply.
  2. 0

    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');
    Login or Signup to reply.
  3. 0

    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.

    Login or Signup to reply.
Please signup or login to give your own answer.

Back To Top
Search