Codewars Kata SQL

Alphabetical Addition

Your task is to add up letters to one letter.

In SQL, you will be given a table letters, with a string column letter. Return the sum of the letters in a column letter.

Notes:

• Letters will always be lowercase.
• Letters can overflow (see second to last example of the description)
• If no letters are given, the function should return 'z'

Examples:

table(letter: ["a", "b", "c"]) = "f"
table(letter: ["a", "b"]) = "c"
table(letter: ["z"]) = "z"
table(letter: ["z", "a"]) = "a"
table(letter: ["y", "c", "b"]) = "d" -- notice the letters overflowing
table(letter: []) = "z"

Confused? Start by converting the letters to numbers: a => 1, b => 2, etc. Add them up. Think about the overflow yourself. Once that’s done, convert it back to a letter.

Hint:

1. The SQL CHR function accepts an ASCII code and returns the corresponding character.
2. The COALESCE function in SQL server is used to return the first Non-NULL value from the list of columns/arguments given in order.
3. COALESCE is considered similar to writing a CASE statement expression in SQL.

Solution:

SELECT COALESCE(CHR(CAST((SUM(ascii(letter) - 96)-1)%26 as int)+97), 'z')
as letter
FROM letters;

Link

Reference