Select with subquery

Question

Select with subquery

Navigation

#1 by (2 votes)

1

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

That returns me:

id_usuario  valor
1           3000
2           2400

When I put this select as a subquery, taking 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

Give the error:

1242 - Subquery returns more than 1 row

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

mysql sql-select select

asked by anonymous 07.12.2016 / 12:14

1 answer

Javascript Arithmetic Error [duplicate] Save value to an input after inserting it automatically using Javascript

score 2 · Accepted Answer

This error occurs because your query returns more than one result, which is not possible since you want to place 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.