CODEWITHSUNDEEP
mysql
Marco
Marco

Reputation: 2737

count how many songs there are according to a specific alphabet

I have a table that contains songs. I want to count how many songs there are according to a specific alphabet.

The table is called ‘song’ and the column name is called ‘name’ The alphabet is given by myself since i’m dealing with a spanish alphabet that contains characters in the alphabet like ‘ñ’, ´ll´,....

The result should be something like this:

alphabet    total
---------------------------------
a           43
b           5
c           0 (or NULL)
d           34
....
n           25
ñ           7

I’m able to pick the song’s first character of the alphabet like this and group them to get a total

SELECT LOWER(LEFT(name, 1)), COUNT(*)
FROM song
GROUP BY LOWER(LEFT(name, 1))

But, if there’s no song that start with the letter K, it won’t show up in the results, and i want to show up as 0 or NULL

How do i achieve this?

Please forgive me, english is not my first language. If im not clear, i will try to answer all your questions. Thanks

Upvotes: 0

Views: 79

Answers (1)

chishaku
chishaku

Reputation: 4643

There might be a faster way but this is one.

Create an alphabet table with a letter column and add all your letters.

Then add a first_letter column to your songs table:

ALTER TABLE song ADD COLUMN first_letter VARCHAR(1);

UPDATE song
SET first_letter = LOWER(LEFT(name, 1))

Then query both tables:

SELECT a.letter, count(s.name)
FROM alphabet a
LEFT JOIN song s ON (a.letter = s.first_letter)
GROUP BY a.letter

Upvotes: 1

Related Questions