SQL Server administration and T-SQL development, Web Programming with ASP.NET, HTML5 and Javascript, Windows Phone 8 app development, SAP Smartforms and ABAP Programming, Windows 7, Visual Studio and MS Office software
Development resources, articles, tutorials, code samples, tools and downloads for ASP.Net, SQL Server, Reporting Services, T-SQL, Windows, AWS, SAP HANA and ABAP


SQL Count Distinct Select Query Example

To count distinct values in a database table or sql view, Transact-SQL developers can use SQL Count Distinct Select command as shown in this SQL tutorial. Sometimes you wonder the count of distinct values stored in a column rather than the total rows count. In this case running a SELECT statement with Count Distinct functions will give you the desired result.

Here is DDL (Data Definition Language) commands for our SQL count distinct query example database table. The sample sql table name is SalesOrders where ordered products are stored. I keep it sample like ignoring foreign keys etc. This table will store different orders for each seperate product. So sales people can request database administrators or SQL developers the number of differet product codes ordered within a certain period of time. In this case, SQL programmers can execute SQL Count Distinct query on SalesOrders table.

Create Table SalesOrders (
 OrderId int identity(1,1),
 CustomerId int,
 ProductId varchar(10),
 Quantity int,
 OrderDate datetime
)
Code

Let's populate sample Count Distinct SQL table with sample data.

Insert Into SalesOrders VALUES ('FLOWERRED9', 29, '20130110')
Insert Into SalesOrders VALUES ('FLOWERRED3', 1, '20130121')
Insert Into SalesOrders VALUES ('FLOWERRED3', 9, '20130131')
Insert Into SalesOrders VALUES ('FLOWERRED9', 3, '20130206')
Insert Into SalesOrders VALUES ('FLOWERRED4', 1, '20130212')
Insert Into SalesOrders VALUES ('FLOWERRED1', 1, '20130214')
Insert Into SalesOrders VALUES ('FLOWERRED3', 1, '20130215')
Code

If you build your SQL SELECT statement to fetch the number of distinct product values inserted into sample database table, you can use the following SQL SELECT COUNT DISTINCT statement.

-- Correct SQL Count Distinct SELECT statement syntax
SELECT COUNT(DISTINCT ProductId) FROM SalesOrders
Code

Although we have insert 7 rows into sample sql table, there are only 4 different products used. And the last SQL Server Count Distinct query returns 4 as the distinct count Select statement.

What you should take care is using the Count and Distinct functions in wrong order. If you use the SQL Count Distinct Select statement as follows, the returned result will be false. So take care to the syntax.

-- This is wrong syntax to use SQL Count Distinct in SELECT statement
SELECT DISTINCT COUNT(ProductId) FROM SalesOrders -- Returns false data
Code


SQL Server

SQL Server 2019 Installation
download SQL Server 2019
download SQL Server 2017
download SQL Server 2016
download SQL Server 2014
download SQL Server 2012
MacOS ve SQL Server 2019


Copyright © 2004 - 2021 Eralper YILMAZ. All rights reserved.