Results 1 to 4 of 4
  1. #1
    Join Date
    Nov 2011
    Posts
    5

    Unanswered: MySQL work with SUM()

    I have a new question based on this two tables:
    1. IN_Order(price, order_amount, order_date, BENR(FK), BANR(FK)
    2. Product (Product_name, product_price, BANR(PK)

    The question i will answer is: Which product sales best and how much?
    For answer this question I need to use 2 rows; Product_name and order_amount.
    I think the best way to solve the question is with sum() function and function for max().

    Id tried this code:
    Select product_name, sum(order_amount) from Product p, IN_Order i WHERE order_amount = (select max(order_amount from in_order) AND i.banr=p.banr;

    Results: Products sold most at one time. This is not the right answar.
    What should I do to answer my question?

  2. #2
    Join Date
    Apr 2002
    Location
    Toronto, Canada
    Posts
    20,002
    Quote Originally Posted by DaKlonker View Post
    I think the best way to solve the question is with sum() function and function for max().
    yes, you need SUM, but not MAX

    this sure sounds like a homework assignment, the classic "best total sales" scenario

    it requires GROUP BY

    Id tried this code:
    and surely got a syntax error
    rudy.ca | @rudydotca
    Buy my SitePoint book: Simply SQL

  3. #3
    Join Date
    Nov 2011
    Posts
    5
    Thanks

    Id tried this code with SUM() and Group by():
    Select product_name, sum(order_amount) from Product p, IN_Order i WHERE i.banr=p.banr group by product_name;

    With this code a get a list of all product with each order amount. I just want to get the product with the highest order amount

  4. #4
    Join Date
    Apr 2002
    Location
    Toronto, Canada
    Posts
    20,002
    add an ORDER BY clause with the LIMIT option
    rudy.ca | @rudydotca
    Buy my SitePoint book: Simply SQL

Posting Permissions

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