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 dupl开发者_开发技巧icates, 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  

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

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

You'd want the COUNT operator.

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

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

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