Re: Counting duplicates in SQL

"Robert Klemme" <shortcutter_at_googlemail.com> wrote in message news:4lttnpF3ffagU1_at_individual.net...
> Jack wrote:
>> Hi
>>
>> I have an input table which can have multiple duplicate rows - same name
>> field different ID's.
>> I can return the duplicate values using the SQL below, but would also
>> like
>> the number of rows containing each duplicate value
>>
>> I have:
>>
>> SELECT Product_ID, Product_Name,
>> (SELECT MIN(Product_ID) FROM Products AS P1
>> WHERE Products.Product_Name = P1.Product_Name) AS OldID
>> FROM Products WHERE Product_ID NOT IN
>> (SELECT MIN(Product_ID) FROM Products AS P2
>> WHERE Products.Product_Name = P2.Product_Name)
>>
>> 85157 Chips 84050
>> 82103 Fish 76929
>> 82103 Fish 76929
>> 99999 Sausage 82785
>> 83780 Sausage 82785
>>
>>
>> So for example for "Sausage" I would like to report how many rows have an
>> ID
>> of 82785, 83780 and 99999 respectively. I can't see how I can adapt my
>> statement above. Is there another approach using just SQL?
>
> Hm, this lookes like homework to me. What else did you try?

Hm, looks like you entered the "I'm a patronising twat" competition to me. Received on Sun Sep 03 2006 - 07:19:31 CDT