Results 1 to 7 of 7
  1. #1
    Join Date
    Apr 2010
    Posts
    20

    Unanswered: Help with query please

    Hello to every one I just try to get my head around to
    build a query

    I have a table has 4 columns in it

    Cust_ID , Contact_ID , Order_ID , Prod_ID

    Thats all

    All i want to do is just to get the customer who has placed the most orders

    Any Ideas just a simple query not views or procedures

    Thanks

  2. #2
    Join Date
    Nov 2003
    Posts
    2,933
    Provided Answers: 12
    Show us what you have tried so far and the error message(s) if any (use Copy & Paste!)

  3. #3
    Join Date
    Apr 2010
    Posts
    20

    Please help with query

    Hello to every one I just try to get my head around to
    build a query

    Customers table

    ID BussinessName
    1 Bakers Construction & Industrial Pty Ltd
    2 Promo Welding & Engineering Pty Ltd
    3 A & C Ginardi Concrete Construction Pty Ltd
    4 Danics Concrete & Construction Pty Ltd
    5 Sancon Concrete test Construction Pty Ltd
    6 Maklah Steel Fabrication Pty Ltd
    etc


    I have another

    Customerdetails table has 4 columns in it

    Cust_ID , Contact_ID , Order_ID , Prod_ID
    5 1 1 1
    5 2 1 1
    5 3 1 1
    2 4 2 2
    11 5 3 3
    6 6 4 4
    6 7 4 4
    1 8 5 5
    4 9 6 6
    4 10 7 7
    16 11 8 8


    I have tried that

    select Customers.CustomerID,Customers.BussinessName, count(*) as 'Orders' from Customers
    inner join CustomerDetails on Customers.CustomerID = CustomerDetails.Cust_ID group by
    Customers.CustomerID,Customers.BussinessName


    Thats all

    All i want to do is just to get the customer.BussinessName who has placed the most orders

    Any Ideas just a simple query not views or procedures

    Thanks

  4. #4
    Join Date
    Apr 2002
    Location
    Toronto, Canada
    Posts
    20,002
    Quote Originally Posted by anthitis View Post
    I have tried that
    that's pretty good

    now add an ORDER BY clause
    rudy.ca | @rudydotca
    Buy my SitePoint book: Simply SQL

  5. #5
    Join Date
    Apr 2010
    Posts
    20

    Please help with query

    Thanks mate i found the answer before sent me
    reply thanks any how

    As you can see The question is
    Retrieve the name of the customer who has placed the most orders
    So all is asking only the name of the company
    I very hapy about the result thanks

    SELECT distinct(CustomerDetails.Cust_ID),Customers.Bussin essName from CustomerDetails,Customers
    Where CustomerDetails.Order_ID = 1 and Customers.CustomerID = 5

  6. #6
    Join Date
    Dec 2007
    Location
    Richmond, VA
    Posts
    1,328
    Provided Answers: 5
    That is not correct based on the description you gave of what you wanted. You said you wanted a query that would get the customer with the most orders. The query you posted above gives you the name of cust id 5 for order id 1. Rudy gave you a hint with the order by, but you might also want a fetch first. Also you could write a query that isolates the customer ids with their counts and then select the max count to join to your table that has the business name. Last tip, DISTINCT is not required.
    Dave

  7. #7
    Join Date
    Dec 2007
    Location
    Richmond, VA
    Posts
    1,328
    Provided Answers: 5
    Also, wouldn't the customer who has placed the most orders be cust id 4? Granted 5 got more stuff, but all on one order. One other item that puzzles me with your description is that there is a different contact id for each product on an order, wouldn't a customer have the same contact info for each item on an order?
    Dave

Posting Permissions

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