Conditional Sum values in mysql query

Question

i have a mysql table that contains services for invoices.

it looks like

servicename|ammount|price|tag(type of service)|discount(service worthy of discount, true or false)|clientDiscount(client worthy of discount, true or false)

I need to make a query that groups by year, sums(amount * price)

But, if discount and clientDiscount are true i need to count for a discount of 15%

I have tried with case and if statements but nothing works. I don't really know witch end to start in. And to be honest i can't really grasp how case and if statements work. I think i need to make one Sum that contains services with discount true and client discount true, then another sum that includes the services without discounts and add these upp to get a correct sum? Right now its like an ant farm in my head.

Answer 1

You want this:

select year(invoicedate), sum(amount * price * if(discount and clientdiscount, 0.85, 1)) total
  from invoices
  group by year(invoicedate);

demo here

The if statement works like this. if(condition, value-if-true, value-if-false). So in your example, if discount and clientdiscount is true (ie, they are both 1), it will return your discount amount of 15% (a discount of X is the same as multiplying by 1 - X). If the condition is false, it will return 1, which won't modify the result of amount * price. In summary, after the if is processed, the sum becomes either:
sum(amount * price * 0.85) -- if discount and client discount are both 1
OR
sum(amount * price * 1) -- if discount and client discount are both 0

Answer 2

You could aggregate it without if. discount and clientdiscount are integers, so you may simply multiply it with your discount percent. Slightly modified script of @pala:

select year(invoicedate), sum(amount * price * (1 - discount * clientdiscount * 0.15)) total
  from invoices
  group by year(invoicedate);