CODEWITHSUNDEEP
sqlsql-serversum
Mirko Fogazzi
Mirko Fogazzi

Reputation: 183

Multiple discount in sql query

I have a table like this:

----------------------------table---------------------------
item     price     discount_1%     discount2_%     quantitiy
------------------------------------------------------------
Crew     5.00      10              20              5

I need to calculate the net prices for each row. Now i use this query:

SELECT item, SUM((price * quantity) - ((price * quantity) * (discount_1% /100))) AS tot
FROM table.

Now this query works pefectly for the first discount but i don't know how to calculate the second one...

The result must be:

--------------
item     tot
--------------
Crew     18

Upvotes: 0

Views: 4443

Answers (1)

Lord Peter
Lord Peter

Reputation: 3501

If the second discount is applied to the net price AFTER the first discount has been taken then you need to multiply the effect of the two discounts:-

select 
item, 
price * 0.01 * (100 - discount_1%) * 0.01 * (100 - discount_2%) * quantity as total
from table
group by
item

Upvotes: 2

Related Questions