Group by - first letter or a range of first letters
-
Hi all, need help on how to go about writing a script that will count and group by the first letter of a field. The field is an nvarchar(100) that contains a company name. I want to know how many companies start with the letter 'A' the letter 'B' and so on. I ultimately want to see the distribution of companies for each letter so I can assign to my five data-entry people a somewhat equal distribution of records to update. Of course, if it's possible (wish wish) having me play with the parameters of the script (ranges say from a-f or a-g, etc.) and doing the group by on a range would be most beneficial -- but I'm sure more work.
Any help would be appreciated! Thanks!David
-
This ain't necessarily pretty or efficient but it works:
Select
substring(YourColumn,1,1) as substr,
count(*)
from
YourTable
GROUP BY substring(YourColumn,1,1)
-
Perfect! Worked like a charm. Thanks a million, Pam.
Viewing 3 posts - 1 through 2 (of 2 total)
You must be logged in to reply to this topic. Login to reply