Count Unique Values Per Column using SQL Server

I was working on a query where unique values in columns had to be counted in SQL Server 2005/2008. Here’s how to do it if you have a similar requirement

Sample Data

CREATE TABLE #TT (ID int, Name varchar(20), AreaCode int)

INSERT INTO #TT (ID, Name, AreaCode)
SELECT 1, 'Mahesh', 2354 UNION ALL
SELECT 2, 'Annie', 2387 UNION ALL
SELECT 3, 'Mahesh', 2354 UNION ALL
SELECT 4, 'Timothy', 2752 UNION ALL
SELECT 5, 'Annie', 2387 UNION ALL
SELECT 6, 'Barrymore', 2354 UNION ALL
SELECT 7, 'Raunak', 2976 UNION ALL
SELECT 8, 'Suprotim', 2998

Count Distinct Values in One Column

SELECT COUNT(DISTINCT AreaCode) as DistinctValues
FROM #TT

OUTPUT

Count Distinct Values in Multiple Columns

-- Count Distinct Values in Multiple Columns
;WITH CTE AS
(
SELECT
ROW_NUMBER() OVER(PARTITION by Name order by Name) AS ColName,
ROW_NUMBER() OVER(PARTITION by AreaCode order by AreaCode) AS ColArea
FROM #TT
)

SELECT
SUM(CASE WHEN ColName = 1 THEN 1 ELSE 0 END) AS DistinctNames,
SUM(CASE WHEN ColArea = 1 THEN 1 ELSE 0 END) AS DistinctAreaCodes
FROM CTE

OUTPUT

About The Author

Suprotim Agarwal, MCSD, MCAD, MCDBA, MCSE, is the founder of DotNetCurry, DNC Magazine for Developers, SQLServerCurry and DevCurry. He has also authored a couple of books 51 Recipes using jQuery with ASP.NET Controls and a new one recently at The Absolutely Awesome jQuery CookBook.

Suprotim has received the prestigous Microsoft MVP award for nine times in a row now. In a professional capacity, he is the CEO of A2Z Knowledge Visuals Pvt Ltd, a digital group that represents premium web sites and digital publications comprising of Professional web, windows, mobile and cloud developers, technical managers, and architects.

Get in touch with him on Twitter @suprotimagarwal, LinkedIn or befriend him on Facebook