Drathier
Drathier

Reputation: 14509

Count all duplicates of each value

I would like a SQL query for MS Jet 4.0 (MSSql?) to get a count of all the duplicates of each number in a database.

The fields are: id (autonum), number (text)

I have a database with a lot of numbers.

Each number should be returned in numerical order, without duplicates, with a count of all duplicates.

Number-fields containing 1, 2, 2, 3, 1, 4, 2 should return:

1, 2  
2, 3  
3, 1  
4, 1  

Upvotes: 28

Views: 133345

Answers (5)

Arjun Singh
Arjun Singh

Reputation: 11

If you want to check repetition more than 1 in descending order then implement below query.

SELECT   duplicate_data,COUNT(duplicate_data) AS duplicate_data
FROM     duplicate_data_table_name 
GROUP BY duplicate_data
HAVING   COUNT(duplicate_data) > 1
ORDER BY COUNT(duplicate_data) DESC

If want simple count query.

SELECT   COUNT(duplicate_data) AS duplicate_data
FROM     duplicate_data_table_name 
GROUP BY duplicate_data
ORDER BY COUNT(duplicate_data) DESC

Upvotes: 0

Duncan Howe
Duncan Howe

Reputation: 3025

This is quite simple.

Assuming the data is stored in a column called A in a table called T, you can use

select A, count(A) from T group by A

Upvotes: 6

Joe Stefanelli
Joe Stefanelli

Reputation: 135729

SELECT number, COUNT(*)
    FROM YourTable
    GROUP BY number
    ORDER BY number

Upvotes: 24

cetver
cetver

Reputation: 11829

SELECT   col,
         COUNT(dupe_col) AS dupe_cnt
FROM     TABLE
GROUP BY col
HAVING   COUNT(dupe_col) > 1
ORDER BY COUNT(dupe_col) DESC

Upvotes: 44

dpmattingly
dpmattingly

Reputation: 1321

You'd want the COUNT operator.

SELECT NUMBER, COUNT(*) 
FROM T_NAME
GROUP BY NUMBER
ORDER BY NUMBER ASC

Upvotes: 5

Related Questions