How to use conditional SUM in MySQL with IF statement

Other Questions MySQL
1

I have a table with the following structure:

product_id | category | amount
10         | type_x   | 300
11         | type_x   | 250
12         | type_y   | 150

I’m trying to get separate totals for each category using this query:

SELECT
    IF(category = 'type_x', SUM(amount), 0) AS type_x_total,
    IF(category = 'type_y', SUM(amount), 0) AS type_y_total
FROM products

But I’m getting wrong results:

type_x_total | type_y_total
700          | 0

The problem is that SUM is calculating all rows together. I need to get the correct totals for each category separately. Also, I don’t understand why type_y shows 0 instead of the total sum. Can someone explain what’s happening here and how to fix it?

I want the output to be:

type_x_total | type_y_total
550          | 150
2

you gotta use SUM with your if inside like this: SUM(IF(category = 'type_x', amount, 0)). this way it only sums up the rows that match. also, don’t forget to GROUP BY the category if you want separate totals!

3

This happens because MySQL runs the IF statement after SUM has already crunched all the rows together. When you write IF(category = 'type_x', SUM(amount), 0), SUM processes everything first, then IF checks whatever category ended up in that final aggregated row.

Flip the conditional logic inside the SUM function instead:

SELECT
    SUM(IF(category = 'type_x', amount, 0)) AS type_x_total,
    SUM(IF(category = 'type_y', amount, 0)) AS type_y_total
FROM products

Now SUM checks each row individually - it adds the amount when the condition matches, otherwise adds 0. I use this pattern all the time for reporting queries where you need to pivot categories into columns.

4

MySQL’s treating all your rows as one group because there’s no GROUP BY clause. It runs SUM on everything before checking the IF condition. Fix it by putting the IF inside SUM instead, or use CASE which I think reads better:

SELECT
    SUM(CASE WHEN category = 'type_x' THEN amount ELSE 0 END) AS type_x_total,
    SUM(CASE WHEN category = 'type_y' THEN amount ELSE 0 END) AS type_y_total
FROM products

Both work the same, but CASE works across more database systems. The trick is doing your conditional logic at the row level first, then aggregating.