Posted by Hugo Kornelis on 06/14/05 01:27

On 13 Jun 2005 15:04:05 -0700, Sygen wrote:

>I am attempting to create a simple recordset that would return the
>number of duplicates that exist in a table with a single column. For
>example if I had a table like the following:
>
>ID Reference Amount
>1 123456 1.00
>2 123456 2.00
>3 123 1.00
>
>I would like to get the following result:
>
>ID Reference Amount RecCount
>1 123456 1.00 2
>2 123456 2.00 2
>3 123 1.00 1
>
>Please help!
>Thanks,
>Shawn

Hi Shawn,

Method 1:

SELECT a.ID, a.Reference, a.Amount,
(SELECT COUNT(*)
FROM MyTable AS b
WHERE b.Reference = a.Reference)
FROM MyTable AS a


Method 2:

SELECT a.ID, a.Reference, a.Amount, COUNT(*)
FROM MyTable AS a
INNER JOIN MyTable AS b
ON b.Reference = a.Reference
GROUP BY a.ID, a.Reference, a.Amount


Method 3:

SELECT a.ID, a.Reference, a.Amount, b.Cnt
FROM MyTable AS a
INNER JOIN (SELECT Reference, COUNT(*) AS Cnt
FROM MyTable
GROUP BY Reference) AS b
ON b.Reference = a.Reference


Best, Hugo
--

(Remove _NO_ and _SPAM_ to get my e-mail address)

• Next in forum: Re: predicate locking
• Prev in forum: Re: A curious case of data corruption
• Thread view: Re: Need Help with Adding a Duplicate record count column to Query

[Reply to this message]