Matt - 1 year ago 71

MySQL Question

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 Source

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: