SitePoint Sponsor

User Tag List

Results 1 to 3 of 3
  1. #1
    SitePoint Member
    Join Date
    Nov 2004
    Location
    Mexico
    Posts
    2
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Exclamation Mega advanced query, really need help

    Hi, i'm making a system wich uses php and mysql, i have 3 tables:
    orders_things
    number_order, code, quantity, price_bought

    number_order is auto_increment, code is a bigint with 13 numbers and has sereveral codes with the same number because the price or the quantity are diferent

    orders
    number_order, status

    things
    code, description

    i need to display 3 columns, code, sum(quantity) and description but i cant do it, i need to display the sum(quantity) of every code or just one code, with its code and description, but the quantity depends of the satus, if the status of the number_order is paid the i can add the quantity, if is not paid i can't add that quantity.

    i mean this i have this data in 3 tables


    orders
    number_order status
    1 paid
    2 paid
    3 not paid
    4 paid
    5 not paid


    orders_things
    number_order code quantity price
    1 9999999999999 300000 34.55
    2 9876543217896 34 12.50
    3 9876543217896 3700 12.50
    4 9999999999999 250 12.50
    5 9999999999999 25 34.55

    things
    code descrption
    9999999999999 pants
    9876543217896 shirts

    and if i want the query of all codes i need this
    code description sum(quantity)
    9999999999999 pants 300250
    9876543217896 shirts 34

    if i want just one code, maybe 9876543217896
    code description sum(quantity)
    9876543217896 shirts 34


    i hope you can help me, thanks

  2. #2
    SitePoint Member
    Join Date
    Nov 2004
    Location
    Lima - Perú
    Posts
    2
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)
    I dont know if this works for MySQL but here it is...

    List totals for paid orders for all codes:
    select t.code as code, sum(ot.quantity * ot.price) as total_paid, t.description as description
    from orders o, things t, orders_things ot
    where
    o.number_order = ot.number_order AND
    o.status = 'paid' AND
    t.code = ot.code


    Same but for a single code:
    select t.code as code, sum(ot.quantity * ot.price) as total_paid, t.description as description
    from orders o, things t, orders_things ot
    where
    o.number_order = ot.number_order AND
    o.status = 'paid' AND
    t.code = ot.code AND
    t.code = ?
    (? = code you want to select, ie t.code = 9876543217896)

    Stuff im not sure:
    > sum(ot.quantity * ot.price) or something else.
    > t.code = 9876543217896 or t.code = '9876543217896'

    Oh noticed its just sum(ot.quantity) not the total money paid...

  3. #3
    SitePoint Member
    Join Date
    Nov 2004
    Location
    Mexico
    Posts
    2
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Talking thanks!!!

    thanks you really helped me it works million thanks


Bookmarks

Posting Permissions

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