Geeks With Blogs
ken spencer

Today I wanted to count each unique element in a SQL Server table to make sure they were unique.  Took a bit of playing but this finally works great:

SELECT DISTINCT t.columntocheck, (Select COUNT(columntocheck) from tabletoserach where columntocheck = t.columntocheck) as countof
FROM tabletoserach t

This gives me back a list of all unique items in columntocheck and also counts the number of them.

Of course, theres no where clause on this so be careful if you have a huge table. Mine was really small, so this works just fine.

Posted on Tuesday, August 9, 2011 10:04 AM | Back to top


Comments on this post: tsql: Counting unique elements in a sql server table

# re: tsql: Counting unique elements in a sql server table
Requesting Gravatar...
Duh!

Why do you have to do this? Isn't simpler and has better performance to do the following?

SELECT columntocheck, COUNT(*)
FROM tabletoserach
GROUP BY columntocheck
Left by Nicholas K on Aug 09, 2011 10:34 AM

# re: tsql: Counting unique elements in a sql server table
Requesting Gravatar...
The COUNT function returns the number of rows that matches a specified definition and the DISTINCT statement can be used to return only distinct values. -Dr. Naveed Fazlani
Left by Bryan Keith on Dec 19, 2011 7:59 AM

Your comment:
 (will show your gravatar)


Copyright © xamlnotes | Powered by: GeeksWithBlogs.net