View : name + count GROUP BY

[anselme]

Hi

for MS SQL 2000/2500

I am having a table with Products, a Product be many time in the table

I need a view to return a GROUP BY on Name + Reference and the Count(Name)

Product
Product_ID int NOT NULL,
Name varchar (50) Not Null,
Reference varchar (50) Not Null,
Infos varchar (255) Null,
PDate (smalldatetime) Null


I need a View with 4 columns

MAX(Product_ID) AS ID, Name, Reference, Count(Name) AS Ct
GROUP BY Name, Reference


i can't Get the count in the same view

thank you for helping

[skhanal]

Aggregate function applies to all columns in group, so you can't do count(name), you have to use count(*)

[anselme]

it works fine !

thank you