{"id":3601,"date":"2024-04-01T10:23:43","date_gmt":"2024-04-01T03:23:43","guid":{"rendered":"https:\/\/www.sqlservertutorial.net\/?page_id=3601"},"modified":"2024-04-01T10:25:19","modified_gmt":"2024-04-01T03:25:19","slug":"sql-server-count-distinct","status":"publish","type":"page","link":"https:\/\/www.sqlservertutorial.net\/sql-server-aggregate-functions\/sql-server-count-distinct\/","title":{"rendered":"SQL Server COUNT DISTINCT"},"content":{"rendered":"\n

Summary<\/strong>: in this tutorial, you will learn how to use the SQL Server COUNT DISTINCT<\/code> to get the total number of unique values in a column of a table.<\/p>\n\n\n\n

Introduction to the SQL Server COUNT DISTINCT #<\/a><\/h2>\n\n\n\n

In SQL Server, the COUNT()<\/code> function is an aggregate function<\/a> that returns the total number of rows that meet a certain condition.<\/p>\n\n\n\n

Sometimes, you may want to count the number of distinct values in a column that meets a condition. To do that, you can use the DISTINCT<\/code> option:<\/p>\n\n\n

COUNT(DISTINCT column_name)<\/code><\/span>Code language:<\/span> SQL (Structured Query Language)<\/span> (<\/span>sql<\/span>)<\/span><\/small><\/pre>\n\n\n
The COUNT DISTINCT<\/code> will count duplicate values as one. It’s important to note that the COUNT(DISTINCT)<\/code> function completely ignores NULL<\/code> when counting.<\/p>\n\n\n\n
In practice, you often use the COUNT DISTINCT<\/code> to get the total number of unique values in a column that satisfies a certain condition.<\/p>\n\n\n\n
SQL Server COUNT DISTINCT examples #<\/a><\/h2>\n\n\n\n
Let’s explore some examples of using the SQL Server COUNT DISTINCT<\/code>.<\/p>\n\n\n\n
1) Basic SQL Server COUNT DISTINCT example #<\/a><\/h3>\n\n\n\n
First, create a new table<\/a> called numbers<\/code> that has an id<\/code> column:<\/p>\n\n\n
CREATE<\/span> TABLE<\/span> numbers(\n   id<\/span> INT<\/span>\n);<\/code><\/span>Code language:<\/span> SQL (Structured Query Language)<\/span> (<\/span>sql<\/span>)<\/span><\/small><\/pre>\n\n\n
INSERT<\/span> INTO<\/span> numbers(id<\/span>) \nVALUES<\/span> \n  (1<\/span>), \n  (2<\/span>), \n  (3<\/span>), \n  (3<\/span>), \n  (NULL<\/span>), \n  (NULL<\/span>);<\/code><\/span>Code language:<\/span> SQL (Structured Query Language)<\/span> (<\/span>sql<\/span>)<\/span><\/small><\/pre>\n\n\n
The numbers<\/code> table has duplicate values 3 and NULL<\/code>.<\/p>\n\n\n\n
Third, use the COUNT DISTINCT<\/code> to return the total number of distinct values in the id<\/code> column:<\/p>\n\n\n
SELECT<\/span> \n  COUNT<\/span>(DISTINCT<\/span> id<\/span>) \nFROM<\/span> \n  numbers;<\/code><\/span>Code language:<\/span> SQL (Structured Query Language)<\/span> (<\/span>sql<\/span>)<\/span><\/small><\/pre>\n\n\n
Output:<\/p>\n\n\n
id_count\n-----------<\/span>\n3<\/code><\/span>Code language:<\/span> SQL (Structured Query Language)<\/span> (<\/span>sql<\/span>)<\/span><\/small><\/pre>\n\n\n
In this example, the query returns 3 which includes the numbers 1, 2, and 3. It counts the duplicate number 3 as 1 and ignores the NULL<\/code>.<\/p>\n\n\n\n
\"SQL<\/figure>\n\n\n\n
2) Counting distinct values in a column #<\/a><\/h3>\n\n\n\n
The following example uses the COUNT DISTINCT<\/code> to get the total number of model years in the production.products<\/code> table:<\/p>\n\n\n
SELECT<\/span> \n  COUNT<\/span>(DISTINCT<\/span> model_year) model_year_count \nFROM<\/span> \n  production.products;<\/code><\/span>Code language:<\/span> SQL (Structured Query Language)<\/span> (<\/span>sql<\/span>)<\/span><\/small><\/pre>\n\n\n
Output:<\/p>\n\n\n
model_year_count\n----------------<\/span>\n4<\/code><\/span>Code language:<\/span> SQL (Structured Query Language)<\/span> (<\/span>sql<\/span>)<\/span><\/small><\/pre>\n\n\n
The output indicates that the production.products<\/code> table has four unique model year values.<\/p>\n\n\n\n
3) Counting distinct values with a condition #<\/a><\/h3>\n\n\n\n
The following example uses the COUNT DISTINCT<\/code> to get the total number of model years that are greater than 2017 from the production.products<\/code> table:<\/p>\n\n\n
SELECT<\/span> \n  COUNT<\/span>(DISTINCT<\/span> model_year) model_year_count \nFROM<\/span> \n  production.products \nWHERE<\/span> \n  model_year > 2017<\/span>;<\/code><\/span>Code language:<\/span> SQL (Structured Query Language)<\/span> (<\/span>sql<\/span>)<\/span><\/small><\/pre>\n\n\n
Output:<\/p>\n\n\n
model_year_count\n----------------<\/span>\n2<\/code><\/span>Code language:<\/span> SQL (Structured Query Language)<\/span> (<\/span>sql<\/span>)<\/span><\/small><\/pre>\n\n\n
In this example:<\/p>\n\n\n\n
In the upcoming examples, we’ll use the production.products<\/code> and production.brands<\/code> tables from the sample database<\/a>:<\/p>\n\n\n\n
Second, insert some rows<\/a> into the numbers<\/code> table:<\/p>\n\n\n