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.

Tags - SQL

Last Modified : June 2023