Re: Counting duplicates in SQL

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?

        robert Received on Sat Sep 02 2006 - 12:35:54 CDT