Results 1 to 3 of 3
  1. #1
    Join Date
    Jun 2005
    Posts
    17

    Thumbs up Unanswered: urgent. please respond

    Hello everyone
    I need help with this query.


    select myorder.name, max(myorder.tot) sales, myorder.description product_description,
    round(max(myorder.tot) / totord.total * 100, 2) total_percentage
    from (select c.customer_id, name,first_name || ' ' || last_name salesperson, p.product_id pid, description,sum(i.total) tot from employee e, customer c, c_sales s, c_item i, product p
    where e.EMPLOYEE_ID = c.salesperson_id and c.customer_id = s.customer_id and
    s.order_id = i.order_id and i.product_id = p.product_id
    group by c.customer_id, name,first_name || ' ' || last_name, p.product_id,description order by 1) myorder,
    (select customer_id, sum(i.total) total from c_sales s, c_item i
    where s.order_id = i.order_id group by customer_id order by 1) totord
    where totord.customer_id = myorder.customer_id and myorder.customer_id = 231
    group by myorder.name, myorder.description, totord.total
    order by 1;


    NAME SALES PRODUCT_DESCRIPTION TOTAL_PERCENTAGE

    MA-02148-YYY 204 ACE TENNIS BALLS-3 PACK 3.22

    MA-02148-YYY 941 ACE TENNIS BALLS-6 PACK 14.85

    MA-02148-YYY 175.6 ACE TENNIS NET 2.77

    MA-02148-YYY 1740.2 DUNK BASKETBALL INDOOR 27.47

    MA-02148-YYY 329.4 DUNK BASKETBALL OUTDOOR 5.2

    Currently this query returns all 5 descriptions. I want to return only product_description where maximum(sales) is found.

    Any suggestions on how i can accomplish this?????


    thanks

  2. #2
    Join Date
    Nov 2003
    Location
    Belgium
    Posts
    25

    Smile

    Hi,

    Do you try to put an Having clause at the end of your query like Having max (sales) ?
    Normally the query will return you only the row having the maximum sales.

    Regards,

    Xavier

  3. #3
    Join Date
    Jun 2005
    Posts
    17

    message

    Hello
    I have tried putting having clause like this:
    having max(myorder.tot) = 1740.2

    But this only works for this customer_id 231.
    I need to let query handle that which will compare this above max(myorder.tot) per customer_id, product_id to third inline view somehow.

    I know i need to add third inline view but dont know how that will solve this problem for all the customer_id's.

    thanks

Posting Permissions

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