EN
MS SQL Server - count duplicated rows
0 points
In this article, we would like to show you how to count duplicated rows in MS SQL Server.
Quick solution:
xxxxxxxxxx
1
SELECT
2
[column_name], COUNT([column_name])
3
FROM
4
[table_name]
5
GROUP BY
6
[column_name]
7
HAVING
8
(COUNT([column_name]) > 1);
To show how to count duplicated rows, we will use the following users
table:

Note:
At the end of this article you can find database preparation SQL queries.
In this example, we will count duplicated user names.
Query:
xxxxxxxxxx
1
SELECT
2
[name], COUNT([name]) AS 'count'
3
FROM
4
[users]
5
GROUP BY
6
[name]
7
HAVING
8
(COUNT([name]) > 1);
Result:

create_tables.sql
file:
xxxxxxxxxx
1
CREATE TABLE [users] (
2
[id] INT IDENTITY(1,1),
3
[name] VARCHAR(100) NOT NULL,
4
[email] VARCHAR(100) NOT NULL,
5
[country] VARCHAR(15) NOT NULL,
6
PRIMARY KEY ([id])
7
);
insert_data.sql
file:
xxxxxxxxxx
1
INSERT INTO [users]
2
([name], [email], [country])
3
VALUES
4
('Tom', 'tom1@email.com', 'Poland'),
5
('Tom', 'tom2@email.com', 'Poland'),
6
('Tom', 'tom3@email.com', 'Poland'),
7
('Kim', 'kim1@email.com', 'Vietnam'),
8
('Kim', 'kim2@email.com', 'Vietnam'),
9
('Chris', 'chris1@email.com', 'Spain'),
10
('Chris', 'chris2@email.com', 'Spain'),
11
('Chris', 'chris3@email.com', 'USA');