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.
Schema reminder
DB schema

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 Hint Help Save Run Query


Answers and Discussion Show

select substr (mems.surname,1,1) as letter, count(*) as count 
    from cd.members mems
    group by letter
    order by letter          

This 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 :-)

You'll need the SUBSTR function here, combined with some aggregation.

Keyboard shortcuts:

Other hints: