CODEWITHSUNDEEP
sqlsql-server
Aximili
Aximili

Reputation: 29474

SQL Query to show multiple entries and its count

I got a table like this (just look at Col1 and Col2)

ID   Col1   Col2   Col3
1    1a     2b     vewva
2    1a     2b     ds33
3    1c     2d     sadp
4    1c     2e     c2w
5    1c     2d     2309v
6    1d     2f     2fd3
7    1c     2d     23d3

I need to find duplicates in (Col1, Col2).

What I need is some output like this:

1a 2b --> occurred 2 times
1c 2d --> occurred 3 times

Is there a SQL Query that can produce that?
Including sort by (or group by) Col2 if possible.

(I'm not sure what the database version is, but it's Microsoft SQL Server)

Upvotes: 2

Views: 3097

Answers (2)

Sachin Shanbhag
Sachin Shanbhag

Reputation: 55489

select col1, col2, count(*)
from yourTable
group by col1, col2
having count(*) > 1

Upvotes: 4

nonopolarity
nonopolarity

Reputation: 151036

you can try

select count(*) as c, col1, col2 from foobar group by col1, col2 having c > 1

Upvotes: 3

Related Questions