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