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

Answer 1

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