CODEWITHSUNDEEP
sql-servercount
user6882159
user6882159

Reputation:

Show records in SQL if count is more than 1

I am creating a web app in which I need to print records which are appearing more than once.

I created this table

create table nameandinfo
(
    names nvarchar(40),
    contno nvarchar(40)
)

and inserted 3 records:

select * from nameandinfo

This is the sample data:

Ibibo   998test389
Nizam   959test681
Ibrahim 998test389

Now I count the data by (contno)

SELECT [contno], COUNT(*) contact
FROM nameandinfo
GROUP BY [contno]

and I get the following results:

9594857681  1
9987145389  2

I want to display only the rows for which the count is more than 1:

9987145389  2

What do I need to do?

Upvotes: 0

Views: 4370

Answers (3)

Maulik Modi
Maulik Modi

Reputation: 1306

If the column is part of table that can be manipulated by where clause. When using Aggregate calculations such as sum and count , Having is used for filtering the data.

SELECT contno,COUNT(*) AS Count 
FROM nameandinfo
GROUP BY contno
HAVING COUNT(*)>1

Upvotes: 0

User
User

Reputation: 804

If you want to apply any condition on Group By Column then you need to use Having as below

SELECT [contno], COUNT(*) cnt
FROM nameandinfo
GROUP BY [contno]
HAVING COUNT(*) > 1

Upvotes: 0

Tim Biegeleisen
Tim Biegeleisen

Reputation: 521249

Use HAVING:

SELECT [contno], COUNT(*) contact
FROM nameandinfo
GROUP BY [contno]
HAVING COUNT(*) > 1

Upvotes: 3

Related Questions