Hi,
I struggling with a query i want to write.
I want to do the following:
I is for an order in a webshop. Customers can buy 1 product of multiple. That gives 2 order rows with two different order prices. But sometimes customers use a discount coupon that contains a negative order price.
The database is build up as follow:
Tables (and columns):
- shop_order (contains all orders)
+ order_id
- shop_orderregel (orderline)
+ orreg_prijs
+ order_omschrijving (discription)
- shop_product
+ product_id
+ product_inkoop (buying price)
Now i want to select all orderlines and show the profit for each orderline.
That is already done and it gives me the following query:
Quote:
select A.order_id,
B.orreg_omschrijving,
B.orreg_prijs,
C.product_inkoop,
round(((B.orreg_prijs / 119) * 100),2) verkoop_ex,
round((((B.orreg_prijs / 119) * 100)- ((C.product_inkoop * A.order_koers) * B.orreg_aantal) - 3),2) Winst,
from shop_order A, shop_orderregel B, shop_product C
where A.order_id = B.orreg_orderid
and A.order_id = '43392'
and C.product_id = B.orreg_productid
|
verkoop_ex means sale prices excluding VAT
winst means profit
Now that works. It shows all orderlines with the profit etc.
But now i want to subtract the discount amount from each of the order lines. i have build this:
Quote:
select A.order_id,
B.orreg_omschrijving,
B.orreg_prijs,
C.product_inkoop,
round(((B.orreg_prijs / 119) * 100),2) verkoop_ex,
round((((B.orreg_prijs / 119) * 100)- ((C.product_inkoop * A.order_koers) * B.orreg_aantal) - 3),2) Winst,
round((((B.orreg_prijs / 119) * 100)- ((C.product_inkoop * A.order_koers) * B.orreg_aantal) - 3) - (select B.orreg_prijs / (select count(*) from shop_order A, shop_orderregel B where A.order_id = '43392'and A.order_id = B. orreg_orderid and B.orreg_omschrijving not like '%Korting%' and A.order_betaald = 'betaald' ) from shop_order A, shop_orderregel B where A.order_id = B. orreg_orderid and B.orreg_omschrijving like '%Korting%' and order_id = '43392' and A.order_betaald = 'betaald'),2) Winst_Addis
from shop_order A, shop_orderregel B, shop_product C
where A.order_id = B.orreg_orderid
and C.product_id = B.orreg_productid
and A.order_id = '43392'
and A.order_betaald = 'betaald'
order by A.order_id desc
|
the difference is this line:
Quote:
|
- (select B.orreg_prijs / (select count(*) from shop_order A, shop_orderregel B where A.order_id = '43392'and A.order_id = B. orreg_orderid and B.orreg_omschrijving not like '%Korting%' and A.order_betaald = 'betaald' ) from shop_order A, shop_orderregel B where A.order_id = B. orreg_orderid and B.orreg_omschrijving like '%Korting%' and order_id = '43392' and A.order_betaald = 'betaald')
|
Korting means Discount
betaald means payed
That works fine to. But so if you may have noticed i tested this for one order (43392).
But i want to show all orders. And there is the problem. The select in select statement must give 1 result. Other wise it wont work. How do i get the discount amount from that order line for every row in the main query??
So if i have 10 orders. With each of them having 2 order lines. But every one of them has a different discount amount. I want to show the correct value in the profit select.
Order_id | profit | profit - discount |
1 | 5 | 3
2 | 10 | 1
3 | 2 | -2
4 | 6 | 2
5 | 8 | 4
dBforums
>
difficult query
10-08-10, 06:53
Linear Mode
