Re: Counting duplicates in SQL

Jack wrote on 02.09.2006 17:32:
> 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?

SELECT product_id, count(*)
FROM
(

   your statement goes here
)
WHERE product_name = 'Sausage'
GROUP BY product_id

Thomas Received on Sat Sep 02 2006 - 11:08:15 CDT