Mysql sum and subtract query

Question

I need a mysql query which is sum all orders, but subtract where ID 80, for example

I try this

SELECT SUM(Orders) FROM MyBuyers WHERE ID=80 NOT IN

but they don't work

SELECT SUM(Orders) FROM MyBuyers

this query works fine, but i must subtract orders where ID 80

Answer 1

Do you want something like that?

SELECT 
SUM(CASE WHEN ID <> 80 THEN Orders ELSE -1*Orders END) AS totalAmount
FROM MyBuyers

So far, what I've understood:

You want to sum up all the orders except orders having ID = 80.

If ID is equal to 80 then you want to subtract the orders from the aggregated result.

---

Consider this set of sample input

ID   Orders
1      5
21     5
23     5
80     4

If your expected output for this given set of input is 11 then the above query is best fit for this.

And if your expected output is 15 then adopt the following query instead:

SELECT SUM(Orders) FROM MyBuyers WHERE ID <> 80

Answer 2

SELECT SUM(Orders) FROM MyBuyers WHERE ID!=80