Select with subquery

Question

I have the following select:

SELECT id_usuario,
SUM(CASE p.tp_honorario WHEN 'Mensal' THEN (h.vl_honorario * 12) ELSE h.vl_honorario END) as valor
FROM empresa_honorario as h 
INNER JOIN honorario as p ON p.id_honorario = h.id_honorario 
WHERE h.id_empresa = 86 OR p.tp_honorario = 'Mensal'
GROUP BY h.id_usuario

Who returns to me:

id_usuario  valor
1           3000
2           2400

When I put this select as subquery, I take only the valor, and filtering for each user:

SELECT SUM(e.vl_imposto) as imposto, SUM(e.vl_total) as vl_total, u.nm_usuario,

(SELECT SUM(CASE p.tp_honorario WHEN 'Mensal' THEN (h.vl_honorario * 12) ELSE h.vl_honorario END) as valor
FROM empresa_honorario as h 
INNER JOIN honorario as p ON p.id_honorario = h.id_honorario 
WHERE h.id_usuario = e.id_usuario AND h.id_empresa = 86 OR p.tp_honorario = 'Mensal'
GROUP BY h.id_usuario)

FROM impostos as e 
INNER JOIN usuario as u ON e.id_usuario = u.id_usuario
WHERE e.id_empresa = 86 GROUP BY e.id_usuario

Makes the mistake:

1242 - Subquery Returns more than 1 Row

How to make this subquery consider only one user at a time?

Answer 1

This error occurs because your query returns more than one result, which is not possible since you want to put it in a column of your main query, what you can do is put a limit to return only one result:

SELECT SUM(CASE p.tp_honorario WHEN 'Mensal' THEN (h.vl_honorario * 12) ELSE    h.vl_honorario END) as valor
FROM empresa_honorario as h 
INNER JOIN honorario as p ON p.id_honorario = h.id_honorario 
WHERE h.id_usuario = e.id_usuario AND h.id_empresa = 86 OR p.tp_honorario = 'Mensal'
GROUP BY h.id_usuario
LIMIT 1

---

Or put other conditions on your WHERE, ensuring that only one record is returned.