# Miscellaneous > Structured Query Language (SQL) >  Newbie question - Trouble grouping data

## Sachin Thanki

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

----------


## rmiao

What was the error?

----------


## Sachin Thanki

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

----------


## rmiao

The message tells all, every column in select list should be used in aggregate function or GROUP BY clause.

----------


## Sachin Thanki

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

----------


## rmiao

It's not my suggestion, but the way sql works.

----------


## nosepicker

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

----------


## Sachin Thanki

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

----------


## Sachin Thanki

Hi nosepicker,

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

Sachin

----------

