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

i trying answer question:

how character occur in first position versus second position of string?

using sql query on mysql.

however syntax error.

the code:

select      onechar,      ascii(onechar) asciival,      count(*) cnt,     sum(case when pos = 1 1 else 0 end) pos_1,     sum(case when pos = 2 1 else 0 end) pos_2 (     (select          substring(`city`, 1, 1) onechar,          1 pos     `orders`       len(`city` >= 1 )      union      (select          substring(`city`, 2, 1) onechar,          2 pos     `orders`       len(`city` >= 2)     )  group onechar order onechar 

the error:

you have error in sql syntax; check manual corresponds mysql server version right syntax use near 'group onechar order onechar limit 0, 30' @ line 1

tried several ways without success.

anyone give me light on problem?

the parenthesis incorrect , query missing alias derived table. also, because mysql evaluates booleans 1 or 0 can simplify sum statements. try this:

select onechar, ascii(onechar) asciival, count(*) cnt, sum(pos = 1) pos_1, sum(pos = 2) pos_2 (     select substring(`city`, 1, 1) onechar, 1 pos     `orders` length(`city`) >= 1      union     select substring(`city`, 2, 1) onechar, 2 pos     `orders` length(`city`) >= 2 ) t group onechar order onechar