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 SQL Server and T-SQL Development Tutorials
Development resources, articles, tutorials, samples, codes and tools for .Net, SQL Server, Windows, Windows Phone, SAP and ABAP, like SAP UI5, Screen Personas, etc.




download SQL Server 2016
download SQL Server 2014
download SQL Server 2012



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
)

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')

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

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






Related SQL Resources

SQL Server Articles

SQL Server 2012

SQL Server Tools

SQL Blog

SQL Server 2008 Blog

Certification Exams Blog

Reporting Services Blog

Analysis Services Blog

MS SQL Server Forums







Copyright © 2004 - 2017 Eralper YILMAZ. All rights reserved.
Community Server by Telligent Systems