I have two tables: order and orderitem with the following structures:
order
[order_ID (primary)][base_price][discount][final_price]
orderitem
[item_ID (primary)][order_ID (primary)][price][color]
Note: one order can have many orderitem ( one to many )
Lets assume we have the following sample data:
order
-------------------------------
[1] [100] [10] [90]
[2] [200] [10] [190]
orderitem
-------------------------------
[A] [1] [25] [blue]
[B] [1] [25] [blue]
[C] [1] [25] [blue]
[D] [1] [25] [red]
[E] [2] [100] [blue]
[F] [2] [100] [red]
We got order.base_price from orderitem total price:
order.base_price = SUM(orderitem.price) WHERE orderitem.order_ID = order.order_ID
Then we apply discount as required, therefore:
order.final_price = order.base_price - order.discount
So far so good. As you can see I applied 10% discount for order_ID 1 and 5% discount for order_ID 2. The problem is I want to calculate the SUM of order's final price for red color only but I don't know how to do that in one query. If no discount is applied, then I can do:
SELECT SUM(orderitem.price) WHERE orderitem.color='red'
But since we have discount here, ideally I want to deduct the individual item according to each order's discount percentage. Logic as follow:
STEP 1 - Obtain the discount percentage of each order
percent = order.discount / order.base_price * 100
order_ID = 1 --> percent = [10] / [100] * 100 = 10%
order_ID = 2 --> percent = [10] / [200] * 100 = 5%
STEP 2 - Deduct all red orderitem according to it's order percentage
orderitem = D-1 --> deducted = 25 - 10% = 22.5
orderitem = F-2 --> deducted = 100 - 5% = 95
STEP 3 - Calculate SUM of all deducted value for red color
SELECT SUM(deducted) FROM orderitem WHERE orderitem.color = 'red'
Expected result = 22.5 + 95 = 117.5
How can I do this in a single SQL query? I hope someone can shed some light..
I make it more general so show all color total.
You could add a where color = 'red' instead of GROUP BY color;
SELECT color, SUM(price*discount) as total_discount
FROM
orderitem o
INNER JOIN
(SELECT order_ID, final_price / base_price as discount
FROM orders) d
ON o.order_ID = d.order_ID
GROUP BY color;
Collected from the Internet
Please contact [email protected] to delete if infringement.
Comments