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.