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