Home / String Operations / Count the number of members whose surname starts with each letter of the alphabet
Count the number of members whose surname starts with each letter of the alphabet
Question
You'd like to produce a count of how many members you have whose surname starts with each letter of the alphabet. Sort by the letter, and don't worry about printing out a letter if the count is 0.
Expected Results
| letter | count |
|---|---|
| B | 5 |
| C | 2 |
| D | 1 |
| F | 2 |
| G | 2 |
| H | 1 |
| J | 3 |
| M | 1 |
| O | 1 |
| P | 2 |
| R | 2 |
| S | 6 |
| T | 2 |
| W | 1 |
Your Answer
Your Results
Loading database...
Answers and Discussion
select substr (mems.surname,1,1) as letter, count(*) as count
from cd.members mems
group by letter
order by letterThis exercise is fairly straightforward. You simply need to retrieve the first letter of the member's surname, and do some basic aggregation to achieve a count. We use the SUBSTR function here, but there's a variety of other ways you can achieve the same thing. The LEFT function, for example, returns you the first n characters from the left of the string. Alternatively, you could use the SUBSTRING function, which allows you to use regular expressions to extract a portion of the string.
One point worth noting: as you can see, string functions in SQL are based on 1-indexing, not the 0-indexing that you're probably used to. This will likely trip you up once or twice before you get used to it :-)