Select all columns on a group by throws error

Question

I ran a query against Northwind database Products Table like below

select * from Northwind.dbo.Products GROUP BY CategoryID and i was hit with a error. I am sure you will also be hit by same error. So what is the correct statement that i need to execute to group all products with respect to their category id's.

edit: this like really helped understand a lot

http://weblogs.sqlteam.com/jeffs/archive/2007/07/20/but-why-must-that-column-be-contained-in-an-aggregate.aspx

Answer 1

If you're using GROUP BY in a query, all items in your SELECT statement must either be contained as part of an aggregate function, e.g. Sum() or Count(), else they will also need to be included in the GROUP BY clause.

Because you are using SELECT *, this is equivalent to listing ALL columns in your SELECT.

Therefore, either list them all in the GROUP BY too, use aggregating functions for the rest where possible, or only select the CategoryID.

Answer 2

You need to use an Aggregate function and then group by any non-aggregated columns.

I recommend reading up on GROUP BY.