what is wrong with mysql statement

Question

this is my statement and what I'm trying to do is get the average amount per user per order, running into problems and don't know how to fix it, all help is appreciated

SELECT usuarios.email, AVG(pedidos_onlines.total), 
CASE WHEN p.nombre like '%colombia%' and AVG(pedidos_onlines.total) <34873 then 'A'
 WHEN p.nombre like '%colombia%' and AVG(pedidos_onlines.total) >= 52309 then 'B'
 ELSE 'C'
end as SegmentColombia,
CASE WHEN p.nombre like '%peru%' and AVG(pedidos_onlines.total) <41 then 'A'
 WHEN p.nombre like '%peru%' and AVG(pedidos_onlines.total) >= 62 then 'B'
 ELSE 'C'
end as SegementPeru
CASE WHEN p.nombre like '%peru%' and AVG(pedidos_onlines.total) <13 then 'A'
 WHEN p.nombre like '%peru%' and AVG(pedidos_onlines.total) >= 19 then 'B'
 ELSE 'C'
 end as SEGMENT Ecudador
FROM pedidos_onlines
LEFT JOIN usuarios ON usuarios.id = pedidos_onlines.usuario_id 
LEFT JOIN ciudades as c on c.id = pedidos_onlines.ciudad_id
LEFT JOIN paises as p on p.id = c.pais_id
WHERE usuarios.email IS NOT NULL
GROUP BY usuarios.email, p.nombre

Error Message:

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 'CASE WHEN p.nombre like '%ecuador%' and AVG(pedidos_onlines.total) <13 then 'A' ' at line 10

I dunno, but I'm guessing you do. Do you get an error message? If so, put it in the question. Are the results not what you expect? If so, put both what you are getting and what you want in the question. — Gordon Linoff, Jul 14 '15 at 19:03
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 'CASE WHEN p.nombre like '%ecuador%' and AVG(pedidos_onlines.total) <13 then 'A' ' at line 10 — Christian Novogratz, Jul 14 '15 at 19:05

Reisclef · Answer 1 · 2015-07-14T19:14:21.123

0

You've missed a comma at the end of the second case:

CASE WHEN p.nombre like '%peru%' and AVG(pedidos_onlines.total) <41 then 'A'
 WHEN p.nombre like '%peru%' and AVG(pedidos_onlines.total) >= 62 then 'B'
 ELSE 'C'
end as SegementPeru

needs to be:

CASE WHEN p.nombre like '%peru%' and AVG(pedidos_onlines.total) <41 then 'A'
 WHEN p.nombre like '%peru%' and AVG(pedidos_onlines.total) >= 62 then 'B'
 ELSE 'C'
end as SegementPeru,

Edit:

And also, as pointed out with the second error:

SEGMENT Ecudador

needs to be

`SEGMENT Ecudador`

Or you could remove the space.

edited Jul 14 '15 at 19:14

answered Jul 14 '15 at 19:07

Reisclef

2,056
1
22
25

awesome thanks however now im getting this error message – Christian Novogratz Jul 14 '15 at 19:10
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 'Ecudador, FROM pedidos_onlines LEFT JOIN usuarios ON usuarios.id = pedidos_onlin' at line 13 – Christian Novogratz Jul 14 '15 at 19:10
There's a space: "SEGMENT Ecudador" so it thinks your field has an alias, and then expect a comma, rather than "field 'space' field". If there needs to be a space, you could put a ` symbol around the two names. Or you could remove the space. – Reisclef Jul 14 '15 at 19:11
I hope that helped. If it did, please be sure to mark it as best answer as per the site's tour: http://stackoverflow.com/tour . – Reisclef Jul 14 '15 at 19:21

score 0 · Accepted Answer · answered Jul 14 '15 at 20:46

It should not be far from what you need.

SELECT u.email, AVG(po.total), 
CASE WHEN p.nombre like '%colombia%' and AVG(po.total) <34873 then 'A'
 WHEN p.nombre like '%colombia%' and AVG(po.total) >= 52309 then 'B'
 ELSE 'C'
end as SegmentColombia,
CASE WHEN p.nombre like '%peru%' and AVG(po.total) <41 then 'A'
 WHEN p.nombre like '%peru%' and AVG(po.total) >= 62 then 'B'
 ELSE 'C'
end as SegementPeru,
CASE WHEN p.nombre like '%peru%' and AVG(po.total) <13 then 'A'
 WHEN p.nombre like '%peru%' and AVG(po.total) >= 19 then 'B'
 ELSE 'C'
 end as `SEGMENT Ecudador`
FROM pedidos_onlines as po
LEFT JOIN usuarios as u ON u.id = po.usuario_id 
LEFT JOIN ciudades as c on c.id = po.ciudad_id
LEFT JOIN paises as p on p.id = c.pais_id
WHERE u.email IS NOT NULL
GROUP BY u.email, p.nombre, 
CASE WHEN p.nombre like '%colombia%' and AVG(po.total) <34873 then 'A'
     WHEN p.nombre like '%colombia%' and AVG(po.total) >= 52309 then 'B'
     ELSE 'C'
    end,
    CASE WHEN p.nombre like '%peru%' and AVG(po.total) <41 then 'A'
     WHEN p.nombre like '%peru%' and AVG(po.total) >= 62 then 'B'
     ELSE 'C'
    end,
    CASE WHEN p.nombre like '%peru%' and AVG(po.total) <13 then 'A'
     WHEN p.nombre like '%peru%' and AVG(po.total) >= 19 then 'B'
     ELSE 'C'
     end

what is wrong with mysql statement

2 Answers2