CODEWITHSUNDEEP
sqlsql-serversql-server-2008count
Talib
Talib

Reputation: 1164

count the total number of column field appeared more than once in database

I am trying to run the query to get the total number of repetitions (appeared more than once) for one column called "abc" . I am trying this but not able to achieve.

select COUNT(SELECT DISTINCT card_no, COUNT(*)  AS cnt )

please help, thanks in advance.

For Example below is the column : 

cards
123,
456
,123

Result:

Count
1

As 123 appeared more than once.

Upvotes: 1

Views: 87

Answers (2)

cf_en
cf_en

Reputation: 1661

You want the number of distinct values in the column that are repeated at least once, is that right?

SELECT COUNT(dupes)
FROM (SELECT card_no AS dupes, COUNT(*) cnt FROM table_name
  GROUP BY card_no HAVING COUNT(*) > 1) A

Edit for explanation.

The inner query SELECT card_no AS dupes, COUNT(*) cnt FROM table_name GROUP BY card_no HAVING COUNT(*) > 1 returns only those values that are repeated in your table. The aliases on the columns are necessary because it's a subquery. You can run this query independently of the outer query to see what results it returns.

You have to have the group by on any field that you don't want to aggregate when you're aggregating other fields (e.g. performing a count of records), and the HAVING part is to filter out anything that isn't duplicated (i.e. has a count of 1). HAVING is the way to apply filtering on aggregated fields that you can't have in a WHERE.

The outer query SELECT COUNT(dupes)... is merely counting the number of card_no values returned by the inner query. Since these are grouped, it gives the number of distinct values that are duplicated.

A at the end there sets up an alias for the subquery so that it can be referenced like it's an actual table elsewhere in the query. This is necessary for any subquery in the FROM clause of another query. Effectively the select in the outer query reads SELECT COUNT(A.dupes)... and without the alias A there would be no way to qualify where the dupes field is being referenced from (even though in this case it's implied).

It's also worth noting that the field COUNT(*) cnt isn't required in the SELECT part of the subquery as it isn't being used anywhere else in the query. It will work just as effectively without it, as long as you still have the GROUP BY and HAVING clauses.

Upvotes: 4

abed
abed

Reputation: 250

SELECT
card_no, COUNT(*) AS "Occurrences"
FROM
YourTable
GROUP BY card_no
HAVING 
COUNT(*) > 1

Upvotes: 1

Related Questions