On a page I have links, in the form of banners, to other sites, and they have mine. They're all stored in a database and there is no problem displaying them. However, there's starting to get rather many of them and I want people to be able to view only a few of them at a time by selecting a letter, and then only be shown those of which title starts with that letter.

How could I write a sql query to select and count the number of titles beginning with each letter? That is, if there is 3 titles starting with A, none with B, 1 with C and 5 with D, how would I write to select A-3, C-1, D-5?

And what do you think would be best, to only display the letters A, C and D (as links) or to display all letters but not making links out of the other ones?

Thanks

SELECT COUNT(column) AS rows,fieldlist FROM table WHERE column LIKE 'letter%";

That's not really what I was looking for I think. I want to select all the (first) letters from the title, and how many times they occur. That is, before I have clicked and chosen a letter. I want to let people know how many entries there are for each letter.

Lets say I have this table

title   other fields...
------------------------
A1
A2
A3
C1
D1
D2
D3
D4
D5
------------------------

I basically want an array containing

PHP Code:

array (
   'A' => 2,
   'C' => 1,
   'D' => 5
) 


So that I can display the links, as in
<a href="links.php?show=A">A (2)</a>
<a href="links.php?show=C">C (1)</a>
<a href="links.php?show=D">D (5)</a>

'%' in SQL is a wildcard so "column LIKE 'a%'" will return all records where the column starts with a letter 'a' or 'A'

COUNT(column) will return the number of rows that have matched the WHERE clause.

Yes, I know. So you mean I should run a query like

SELECT COUNT(*) as rows FROM table WHERE title LIKE '$letter%'

and run it for all letters a-z?

I just thought it might be possible to select the first letter, maybe by using a substr-like function, and maybe group by(?) that letter, and somehow also get the number of times that letter occurs. So that I can get the info by running a single query.

you mean like

SELECT COUNT(column) as rows,left(column,1) AS letter FROM table GROUP BY left(column,1) ORDER BY left(column,1);

Exactly! That did it, gave me just what I wanted.
I used
SELECT COUNT(title) as count, left(title,1) as letter FROM links GROUP BY letter ORDER BY title

and it gave me

count | title
------------
  2   |  A
  1   |  C
  5   |  D
------------

Thanks chrishirst