khristoff khristoff - 2 months ago 10
MySQL Question

How count initial letter from value in MySQL?

I have a table called "customers" in MySQL and with

SELECT id, name, left(UPPER(name),1) AS letter


get this:

id | name | letter
1 John J
2 James J
3 Julia J
4 Lauren L
5 Peter P
6 Pietro P


But now I need to get this counting the total of initial letters

id | name | letter | total
1 John J 3
2 James J 3
3 Julia J 3
4 Lauren L 1
5 Peter P 2
6 Pietro P 2


I solved it (temporary) adding

(SELECT COUNT(id) FROM customers WHERE left(UPPER(name),1) = letter) AS total"


, but my original query include many INNER JOIN, so, this last Select duplicates the inner join and wheres (maybe unnecesary).

Any suggestions?

Answer

Join your first query with a subquery that gets the counts for each initial.

SELECT id, name, letter, total
FROM customers AS c
JOIN (SELECT UPPER(LEFT(name, 1)) AS letter, COUNT(*) AS total
      FROM customers
      GROUP BY letter) AS l
ON l.letter = UPPER(LEFT(c.name, 1))