CODEWITHSUNDEEP
oracle-databasegroup-by
Farhan stands with Palestine
Farhan stands with Palestine

Reputation: 14126

How to apply a WHERE condition with count(distinct) and group by in a query?

I have this query

SELECT COUNT(DISTINCT COL1) AS Counter,COL1 FROM TABLE_NAME IG group by COL1

It counts the distinct values present in a column. So, if the table is like 

COL1

AA
AA
AA
BB
BB
CC

The result will be - , based on the above query

Counter   COL1
3         AA
2         BB
1         CC

I want to see all those records for which the counter has value greater than 1, i.e The result must be like suggested below

Counter   COL1
    3         AA
    2         BB

How to apply this WHERE condition on the above query?

Thanks in advance...

Upvotes: 0

Views: 58

Answers (2)

Radu Gheorghiu
Radu Gheorghiu

Reputation: 20499

What you are actually looking for is a different query, with HAVING:

SELECT COUNT(*) AS Counter
    , COL1
FROM TABLE_NAME
GROUP BY COL1
HAVING COUNT(*) > 1

Upvotes: 1

AntDC
AntDC

Reputation: 1917

You're looking for HAVING

SELECT COUNT(DISTINCT COL1) AS Counter,COL1 
FROM TABLE_NAME IG 
GROUP BY COL1
HAVING COUNT(DISTINCT COL1) > 1

Upvotes: 2

Related Questions