Results 1 to 9 of 9

Thread: Newbie question - Trouble grouping data

  1. #1
    Join Date
    Jul 2008
    Posts
    5

    Newbie question - Trouble grouping data

    Hi all,

    I'm having a bit of a problem grouping data together. In a nutshell, i'm trying to extract information from two tables; a Products table and Merchant_Products table.

    The Products table contains a single product and the Merchant_Product contains all linked products. I'm trying to pull out the lowest priced product from Merchant_Products but when i'm using the Group By command i get an error. I'm using the following query to extract the information:

    SELECT Merchant_Products.Merchant_Product_Price, Products.Product_Name, Products.Product_Description, Products.Product_Image_URL,
    Products.Product_ID, Category.Category_ID
    FROM Merchant_Products INNER JOIN
    Products ON Merchant_Products.Product_ID = Products.Product_ID INNER JOIN
    Category ON Products.Cat_ID = Category.Category_ID
    ORDER BY Products.Product_ID

    Any help would be much appreciated as i'm truly stuck.

    Cheers,

    Sach

  2. #2
    Join Date
    Sep 2002
    Posts
    5,938
    What was the error?

  3. #3
    Join Date
    Jul 2008
    Posts
    5
    Hi rmiao,

    I'm not getting the following error message:

    Msg 8120, Level 16, State 1, Line 1
    Column 'Merchant_Products.Merchant_Product_Price' is invalid in the select list because it is not contained in either an aggregate function or the GROUP BY clause.

    Here's the full query:

    SELECT Merchant_Products.Merchant_Product_Price, Products.Product_Name, Products.Product_Description, Products.Product_Image_URL,
    Products.Product_ID, Category.Category_ID
    FROM Merchant_Products INNER JOIN
    Products ON Merchant_Products.Product_ID = Products.Product_ID INNER JOIN
    Category ON Products.Cat_ID = Category.Category_ID
    group BY Products.Product_ID

    If i add the column from the error message in the query then i get another error message which asks me for another column. In a nutshell, the error messages runs through all the table columns which i've defined. If i add all of them then the all the data comes through, which isn't what i want.

    Thanks in advance, your help is much appreciated.

    Sach

  4. #4
    Join Date
    Sep 2002
    Posts
    5,938
    The message tells all, every column in select list should be used in aggregate function or GROUP BY clause.

  5. #5
    Join Date
    Jul 2008
    Posts
    5
    Hi rmiao,

    If i do what you're suggesting then i get all the results back which isn't what i want. I only want the lowest priced item from the Merchant_Products table which relates to the Product_ID in the Products table.

    Thanks again for your help.

    Sach

  6. #6
    Join Date
    Sep 2002
    Posts
    5,938
    It's not my suggestion, but the way sql works.

  7. #7
    Join Date
    Dec 2004
    Posts
    502
    I think you want to do something like this:

    SELECT
    MinPrices.Product_ID,
    MinPrices.Merchant_Product_Price,
    Products.Product_Name,
    Products.Product_Description,
    Products.Product_Image_URL,
    Category.Category_ID
    FROM
    (SELECT
    Products.Product_ID,
    MIN(Merchant_Products.Merchant_Product_Price) AS Merchant_Product_Price
    FROM Merchant_Products
    JOIN Products
    ON Merchant_Products.Product_ID = Products.Product_ID
    JOIN Category
    ON Products.Cat_ID = Category.Category_ID
    GROUP BY Products.Product_ID) AS MinPrices

    JOIN Products
    ON MinPrices.Product_ID = Products.Product_ID

    JOIN Category
    ON Products.Cat_ID = Category.Category_ID

  8. #8
    Join Date
    Jul 2008
    Posts
    5
    Hi nosepicker,

    Thanks for the suggestions, i'll test it tonight and let you know how i get on.

    Can anyone suggest any sites that i could use to improve my knowledge on writing SQL query scripts?

    Thanks

    Sachin

  9. #9
    Join Date
    Jul 2008
    Posts
    5
    Hi nosepicker,

    I've had a quick look and it seems to work! Thanks for your help; it really has helped me out!

    Sachin

Posting Permissions

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