Matt Matt - 3 months ago 13
MySQL Question

Unexpected values in MySQL SELECT CONCAT() column output

I have a CONCAT function setup as follows (full SQL at the bottom):

concat('Disadvantaged (', sum(1), ')') as 'Focus Group'


I'm expecting the word Disadvantaged followed by a total in brackets as the query is grouped i.e.
Disadvantaged (39)


However, instead I am getting:
446973616476616e74616765642028333929


Here's my full query:

SELECT Subject,
concat('Disadvantaged (', sum(1), ')') as 'Focus Group',
Avg(G1.Pointscore) as 'Average Result',
Avg(G2.Pointscore) as 'Average KS4 Target',
Avg(G1.Pointscore - G2.Pointscore) as 'Average Residual',
sum(1) as 'No. Students',
/* Attainment totals */
sum(case when G1.Pointscore >= 7 then 1 else 0 end) as 'No. A*-A',
sum(case when G1.Pointscore >= 5 then 1 else 0 end) as 'No. A*-C',
/* Attainment percentages */
sum(case when G1.Pointscore >= 7 then 1 else 0 end) / sum(1) as 'A*-A',
sum(case when G1.Pointscore >= 5 then 1 else 0 end) / sum(1) as 'A*-C',
/* Progress totals */
sum(case when G1.Pointscore - G2.Pointscore > 1 then 1 else 0 end) as 'No. Sig Above',
sum(case when G1.Pointscore - G2.Pointscore = 1 then 1 else 0 end) as 'No. Above',
sum(case when G1.Pointscore - G2.Pointscore = 0 then 1 else 0 end) as 'No. On',
sum(case when G1.Pointscore - G2.Pointscore = -1 then 1 else 0 end) as 'No. Below',
sum(case when G1.Pointscore - G2.Pointscore < -1 then 1 else 0 end) as 'No. Sig Below',
/* Progress percentages */
sum(case when G1.Pointscore - G2.Pointscore > 1 then 1 else 0 end) / sum(1) as 'Sig Above',
sum(case when G1.Pointscore - G2.Pointscore = 1 then 1 else 0 end) / sum(1) as 'Above',
sum(case when G1.Pointscore - G2.Pointscore = 0 then 1 else 0 end) / sum(1) as 'On',
sum(case when G1.Pointscore - G2.Pointscore = -1 then 1 else 0 end) / sum(1) as 'Below',
sum(case when G1.Pointscore - G2.Pointscore < -1 then 1 else 0 end) / sum(1) as 'Sig Below'
FROM Students S
INNER JOIN Results R ON S.UPN = R.UPN
INNER JOIN Grades G1 ON Result = G1.Grade
INNER JOIN Grades G2 ON Target = G2.Grade
WHERE Disadvantaged = 'Y'
GROUP BY Subject


UPDATE: I found another post that raises and answers this question here:

Weird behaviour of SUM and CONCAT in MySql

Answer

I don't think you can put the SUM function into the CONCAT function, and I also got gibberish when trying this locally. One workaround would be to wrap your current query, and then use only columns inside CONCAT, e.g.

SELECT t.Subject,
       CONCAT('Disadvantaged (', t.`No. Students`, ')') AS 'Focus Group',
       t.`Average Result`,
       t.`Average KS4 Target`,
       t.`Average Residual`,
       t`.No. Students`,
       ...
FROM
(
    SELECT Subject,
           AVG(G1.Pointscore) AS 'Average Result',
           AVG(G2.Pointscore) AS 'Average KS4 Target',
           AVG(G1.Pointscore - G2.Pointscore) AS 'Average Residual',
           SUM(1) AS 'No. Students',
           ...
    FROM Students S
    ...
) t

Update:

I also tried switching to the pipe operator for concatenation via:

SET sql_mode = 'PIPES_AS_CONCAT'

followed by your concatenation with SUM(1) via

SELECT 'Disadvantaged (' || SUM(1) || ')'

but this also did not work. Here is a Fiddle appearing to show that switching to the pipe operator also does not work:

SQLFiddle