Results 1 to 6 of 6
  1. #1
    Join Date
    Oct 2012
    Posts
    2

    Unanswered: Need help with SUBQUERIES (Homework)

    Hello everyone, I am really stuck with this homework assignment. So far I have understood most of the SQL material until now. I been working on this Homework assignement for hours and still can't find a way to get it to wok. I really could use some Input.

    Here is what the teacher askes:

    Display each vendor's name and the number of Products that vendor has available. Label the columns Vendor and Number of Products.
    Sort them in descending order by the number of products, within each number, sort alphabetically by the vendor name.

    This is waht I got sofar:
    Code:
    SELECT	VendName AS [Vendors],
    		(SELECT	COUNT(ProductNumber)
    		FROM ProductVendors
    		WHERE ProductVendors.VendorID = Vendors.VendorID) AS [Number of Product]
    FROM Vendors;
    I just don't know how to sort it out in descending order by the number of products, within each number, sort alphabetically by the vendor name?


    One more thing we have not learend about Inner or outer Joins. Just simple subqueries. Well, they are apparently not that simple.
    Last edited by ettedo2000; 10-25-12 at 22:57.

  2. #2
    Join Date
    Oct 2009
    Location
    221B Baker St.
    Posts
    486
    To "sort it ot in descending order" you need to use an ORDER BY which should be explained in the material concerning the SELECT.

    If i understand what you are to do, there will be no need for any join(s) at all.

  3. #3
    Join Date
    Oct 2012
    Posts
    2
    It all comes back to the basic. I must have over thought the whole thing.I thought I had to place the ORDER BY into the Subquerie itself, and (of course)that did not work.

    This is what I did now. It runs and gives me the right output. Would this be correct?

    Code:
    SELECT	VendName AS [Vendors],
    		(SELECT	COUNT(ProductNumber)
    		FROM ProductVendors
    		WHERE ProductVendors.VendorID = Vendors.VendorID) AS [Number of Product]
    FROM Vendors
    ORDER BY (SELECT COUNT(ProductNumber)FROM ProductVendors
    		WHERE ProductVendors.VendorID = Vendors.VendorID)DESC,VendName;
    Thank you for your help

  4. #4
    Join Date
    Feb 2004
    Location
    In front of the computer
    Posts
    15,579
    Provided Answers: 54
    Yes, at least based on what you've written so far about the assignment that would be a correct solution.

    DBForums gets a lot of folks looking for us to do their homework for them. You did a great job of telling us that this was homework, explainging what you had done and what caused you trouble, and asking for a hand instead of a solution. You did a great job, and asked for appropriate help in an appropriate way!

    Welcome to DBForums!

    -PatP
    In theory, theory and practice are identical. In practice, theory and practice are unrelated.

  5. #5
    Join Date
    Jun 2003
    Location
    Ohio
    Posts
    12,592
    Provided Answers: 1
    Your query will work, but is neither the most efficient nor most concise.
    You should avail yourself of the GROUP BY clause, and you should always avoid nested subqueries in your SELECT clause.
    Code:
    select	VendName as [Vendors],
    	COUNT(*) as [Number of Product]
    from	Vendors
    	left outer join ProductVendors on Vendors.VendorID = ProductVendors.VendorID
    group by VendName
    order by COUNT(*) desc
    If it's not practically useful, then it's practically useless.

    blindman
    www.chess.com: "sqlblindman"
    www.LobsterShot.blogspot.com

  6. #6
    Join Date
    Feb 2004
    Location
    In front of the computer
    Posts
    15,579
    Provided Answers: 54
    Quote Originally Posted by blindman View Post
    You should avail yourself of the GROUP BY clause, and you should always avoid nested subqueries in your SELECT clause.
    If it were not for the last sentence in the original post, I would agree with you completely. Because they haven't covered JOIN operations in class yet, I wouldn't recommend it for this assignment even though it is preferable for every reason that you cited (and more).

    -PatP
    In theory, theory and practice are identical. In practice, theory and practice are unrelated.

Posting Permissions

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