December 04, 2010

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


Count Distinct Values SQL

Count Distinct Values in Multiple Columns

-- Count Distinct Values in Multiple Columns
ROW_NUMBER() OVER(PARTITION by Name order by Name) AS ColName,
ROW_NUMBER() OVER(PARTITION by AreaCode order by AreaCode) AS ColArea

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


Count Distinct Values SQL Server

About The Author

Suprotim Agarwal, ASP.NET Architecture MVP works as an Architect Consultant and provides consultancy on how to design and develop Web applications.

Suprotim is also the founder and primary contributor to DevCurry, DotNetCurry and SQLServerCurry. He has also written an EBook 51 Recipes using jQuery with ASP.NET Controls.

Follow him on twitter @suprotimagarwal

No comments: