EN
MS SQL Server - count distinct values
0 points
In this article, we would like to show you how to count distinct values in MS SQL Server.
Quick solution:
xxxxxxxxxx1
SELECT COUNT(DISTINCT [column_name])2
FROM [table_name];xxxxxxxxxx1
SELECT COUNT([column1]) AS [alias_name], [column2], [columnN]2
FROM [table_name]3
GROUP BY [columnN]4
ORDER BY COUNT([column1]) DESC;To show how to count distinct values, we will use the following table:
Note:
At the end of this article you can find database preparation SQL queries.
In this example, we will count the number of unique countries in users table.
Query:
xxxxxxxxxx1
SELECT COUNT(DISTINCT([country])) AS [unique_countries]2
FROM [users];Output:
In this example, we will count the number of users in every (unique) country in descending order.
Query:
xxxxxxxxxx1
SELECT COUNT([name]) AS [number of users], [country]2
FROM [users]3
GROUP BY [country]4
ORDER BY COUNT([name]) DESC;Output:
create_tables.sql file:
xxxxxxxxxx1
CREATE TABLE [users] (2
[id] INT IDENTITY(1,1),3
[name] VARCHAR(100) NOT NULL,4
[country] VARCHAR(15) NOT NULL,5
PRIMARY KEY ([id])6
);insert_data.sql file:
xxxxxxxxxx1
INSERT INTO [users]2
([name], [country])3
VALUES4
('Tom', 'Poland'),5
('Chris', 'Spain'),6
('Jack', 'Spain'),7
('Kim', 'Vietnam'),8
('Marco', 'Italy'),9
('Kate', 'Spain'),10
('Nam', 'Vietnam');