Re: Counting duplicates in SQL

From: DA Morgan <damorgan_at_psoug.org>
Date: Sun, 03 Sep 2006 19:09:29 -0700
Message-ID: <1157335769.824274@bubbleator.drizzle.com>

Jack wrote:

> "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.

Jack as an instructor at the University of Washington teaching Oracle I am grateful for people who provide hints but not solutions and it is wrong for you to denigrate them.

It is impossible for instructors to monitor all of the ways that students can cheat and self-policing professions are of great value. I am sure you wouldn't want to find yourself working on a team with someone that couldn't pull their weight. Please reconsider your attitude.

Thank you Robert.

-- 
Puget Sound Oracle Users Group

Received on Sun Sep 03 2006 - 21:09:29 CDT