SQL query help

[Zachary Buckholz]

I am updating my Drinks Dictionary iPhone app which uses a sqlite database. I need to create a query to count how many items begin with the letter 'a', 'b', 'c', etc....

I can get as far as 'select DISTINCT substr(name,1,1) FROM drinks;

How can I apply a GROUP BY clause or something else so in one query I can get every unique first character and a count of how many drinks start with that character?

Thanks
Zach

[Sarah Sproehnle]

Hi Zach,

I believe what you want is something like:
SELECT count(*), name FROM Drinks GROUP BY left(name,1);

-Sarah

[Zachary Buckholz]

Thanks Sarah - you got me where I wanted to be.
sqlite> select count(*), substr(name,1,1) from drinks group by substr(name,1,1);

3|'
1|0
5|1
4|2
8|3
4|4
4|5
4|6
3|7
4|8
3|9
312|A
631|B
542|C
233|D
118|E
312|F
322|G
194|H
112|I
171|J
134|K
240|L
389|M
115|N
95|O
397|P
15|Q
294|R
744|S
453|T
25|U
92|V
166|W
5|X
28|Y
28|Z
1|�