MySQL subtract from SUM in CASE statement

Question

I have an SQL code.

SELECT DISTINCT SQL_CALC_FOUND_ROWS 
                 sales.code as code,
                 SUM(CASE WHEN sales.type = 51 THEN sales.amount ELSE 0 END) AS amount,
                 sales.type as type
              FROM sales

Now the thing is that I need to subtract the sales.amount from the total SUM IF the sales.type != 51 AND sales.amount > 0. How could I do it? I guess I need another CASE statement inside the first one after ELSE statement, but I don't know how this works.

As example if the type is not 51 and it has sales.amount bigger than 0, then I should subtract it from the amount.

Thanks.

Answer 1

Where is this select distinct coming from? Use GROUP BY:

SELECT s.code as code,
       SUM(CASE WHEN s.type = 51 THEN s.amount ELSE 0 END) AS amount
FROM sales s
GROUP BY s.code;

Then the answer to your question is to change the ELSE clause:

SELECT s.code as code,
       SUM(CASE WHEN s.type = 51 THEN s.amount ELSE - s.amount
           END) AS amount
FROM sales s
GROUP BY s.code;