Results 1 to 2 of 2

Thread: difficult query

  1. #1
    Join Date
    Oct 2010
    Posts
    1

    Unanswered: difficult query

    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:
    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:

    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:

    - (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

  2. #2
    Join Date
    Sep 2009
    Location
    San Sebastian, Spain
    Posts
    880
    Hi,

    SQL permits the FROM definition to include a SQL query. In this case you need to keep track of the number of orders and this can be done in a query. Here is an example:

    Code:
    SELECT A.order_id, A.cost / b.numrows
      FROM shop_order A,
               (SELECT order_id, count(1) numrows
                  FROM shop_order
                GROUP BY order_id) B
     WHERE A.order_id = b.order;
    Ronan Cashell
    Certified Oracle DBA/Certified MySQL Expert (DBA & Cluster DBA)
    http://www.it-iss.com
    Follow me on Twitter

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •