CODEWITHSUNDEEP
sql
PBJu
PBJu

Reputation: 1

Counting duplicate records within duplicate records using SQL select statement

I have a table with duplicate tag_id, and serial numbers. How could I display a list of distinct tag_id and the number of serial numbers related to it. In other words, I need to know how many serial numbers each distinct tag_id has.

Upvotes: 0

Views: 473

Answers (2)

Mr Moose
Mr Moose

Reputation: 6344

Similar to what Dyrandz had, but counting the number of serial numbers per tag id as you requested.

DECLARE @YourTable AS TABLE (
    tag_id     INT,
    serial_no  VARCHAR(100)
)

INSERT INTO @YourTable (tag_id, serial_no)
VALUES (1, 'abc'), (2, 'cde'), (3, 'def'), (1, 'xyz'), (3, 'lmn')


SELECT tag_id, COUNT(serial_no) AS TotalSerialNumbers
FROM @YourTable
GROUP BY tag_id
ORDER BY 2 DESC

Upvotes: 0

Dyrandz Famador
Dyrandz Famador

Reputation: 4525

Try this query.. 

 SELECT YourColumn, COUNT(*) TotalCount
    FROM YourTable
    GROUP BY YourColumn
    HAVING COUNT(*) > 1
    ORDER BY COUNT(*) DESC

Upvotes: 1

Related Questions