Count number of identical record fields

  • Thread starter Thread starter Hugh Fox
  • Start date Start date
H

Hugh Fox

I have a large database which contains 4 columns: serialidd; catalogid;
orderid; keycode

I want to count the number of times identical strings occur in the keycode
column.

I'm a newbie (and VERY occasional user) at SQL, so please forgive the
ignorance ... I'm attaching to an MSSQL database through Access to do these
queries.

So far my query is:

SELECT serialidd, catalogid, orderid, keycode, COUNT(DISTINCT keycode)
AS CodeCount
FROM dbo.keycodes
GROUP BY serialidd, catalogid, orderid, keycode
HAVING (NOT (orderid IS NULL))

.... but that is not correct.

Could any of the very helpful gurus please give me a hand.

TIA,

Hugh
 
If you just want to know which keycodes have duplicates:
SELECT KeyCode, Count(OrderID) As CountOfOrderID
FROM keycodes
WHERE orderID Is Not Null
GROUP BY keycode;

If you want to see the details of those records, use a subquery:
SELECT serialid, catalogid, orderid, keycode,
FROM keycodes
WHERE (OrderID Is Not Null) AND

( (SELECT Count(KeyCode) FROM Keycodes AS Dupe
WHERE Dupe.keycode = keycodes.keycode) > 0 );
 
Back
Top