How to count the number of distinct field values in a table in SQL Server

Let's say you have a large table of records and want to know the number of distinct values in a field in a table. If you think the following is the answer....

    create table #tmpCar ( make varchar(50))

    Insert into #tmpCar (Make)
        Select distinct make from Cars

    declare @Total int = (Select count(*) from #tmpCar)

You'd be wrong... There's no need to use a temporary table to get the distinct value count.

The answer is much easier and quicker and involves less writing, so its a win....

    declare @Total int = (Select count(distinct make) from cars)

Straightforward and takes no time to implement.

Other Articles of Interest

Next Article : How to test if a file exists in SQL Server
Previous Article : How to do Case Sensitive Searches in SQL Server

Tags - SQL

Last Modified : 29th May 2023
Date Published : 22nd May 2022

Comments and Questions

There's no register feature and no need to give an email address if you don't want to. All messages will be reviewed before being displayed. Comments may be merged or altered slightly, such as if it contains an email or website address.

You can decline to give a name; if that is the case, the comment will be attributed to a random star. A name is preferred, even if it's a random made-up one by yourself.

If you give an email address, you may receive an email notifying you when someone else has added a comment to the same page. In the email will be a link to unsubscribe to further notifications.


About... / Contact.. / Cookie...