Jonathan Livingston Seagull Jonathan Livingston Seagull - 7 months ago 16
SQL Question

How often does a character occur in the first position vs the second position of a string?

I am trying to answer the question:


How often does a character occur in the first position versus the
second position of a string?


using a SQL query on Mysql.

However I get a syntax error.

The code:

SELECT
onechar,
ASCII(onechar) as asciival,
COUNT(*) as cnt,
SUM(CASE WHEN pos = 1 THEN 1 ELSE 0 END) as pos_1,
SUM(CASE WHEN pos = 2 THEN 1 ELSE 0 END) as pos_2
FROM (
(SELECT
SUBSTRING(`city`, 1, 1) as onechar,
1 as pos
FROM `orders`
WHERE LEN(`city` >= 1 )

UNION ALL

(SELECT
SUBSTRING(`city`, 2, 1) as onechar,
2 as pos
FROM `orders`
WHERE LEN(`city` >= 2)
)
GROUP BY onechar
ORDER BY onechar


The error:


You have an error in your SQL syntax; check the manual that
corresponds to your MySQL server version for the right syntax to use
near 'GROUP BY onechar ORDER BY onechar LIMIT 0, 30' at line 1


Tried several ways without success.

Anyone could give me a light on this problem?

Answer

The parenthesis look incorrect and the query is missing an alias for the derived table. Also, because mysql evaluates booleans to 1 or 0 you can simplify your sum statements. Try this:

SELECT onechar, ASCII(onechar) as asciival, COUNT(*) as cnt,
SUM(pos = 1) as pos_1,
SUM(pos = 2) as pos_2
FROM (
    SELECT SUBSTRING(`city`, 1, 1) as onechar, 1 as pos
    FROM `orders` WHERE LENGTH(`city`) >= 1 
    UNION ALL
    SELECT SUBSTRING(`city`, 2, 1) as onechar, 2 as pos
    FROM `orders` WHERE LENGTH(`city`) >= 2
) t
GROUP BY onechar
ORDER BY onechar