Need Help with Adding a Duplicate record count column to Query

[Sygen]

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

[Hugo Kornelis]

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)

[Hugo Kornelis]

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)