Group By query

[Idee]

SELECT ProductGroupId,ObjectId,GroupName,Featured,Hidden,CreatedOn,DisplayOrder, count(productid) as cnt
FROM ProductGroup a, product b
WHERE ObjectId = 31
a.productgroupid = b.productgroupid
GROUP BY a.Productgroupid

This query is not correct because Groupby is only on one field and the fieldnames selected are many. But I want all this info along with number of products in each productgroup.

Could anyone help me?

Thanks

 

[KenReay]

Hi

Cannot give definitive answer, but you need to apply an agregate condition to each of the other selected fields so (I have given example of First() for ObjectId, you need to decide which functions you want to apply and apply them to teh remaining seelcted columns

SELECT ProductGroupId,First(ObjectId) As FisrtObjectId,GroupName,Featured,Hidden,CreatedOn,DisplayOrder, count(productid) as cnt
FROM ProductGroup a, product b
WHERE ObjectId = 31
a.productgroupid = b.productgroupid
GROUP BY a.Productgroupid



Regards

Ken Reay
Freelance Solutions Developer
Boldon Information Systems Ltd
Website needs upgrading, but for now -

http://www.kenneth.reay.btinternet.co.uk

UK