Problem with subquery

Question

Good night,

I need to bring the full subquery result:

(select (valor * 0.05) from tbl_exames) as 'Valor dos Exames',

In a delimiter:

delimiter //
create procedure comissao(idmedico int)
begin
    select nome_medico as 'Médico',
    (select (valor * 0.05) from tbl_exames) as 'Valor dos Exames',
    (select sum(valor * 0.05) from tbl_exames) as 'Valor Total das Comissões'
    from tbl_medico where id_medico = idmedico;
end //
delimiter ;

Where error: Error Code: 1242. Subquery Returns more than 1 Row.

It will list all the values of the tests being 5% of her.

Edit:

I’d like him to appear as follows:

---

Doctor - name

---

Examinations Value - value1, value2, value3, etc..

---

Total Commission Value - Sum of the above values

---

Edit2:

It would be so, but next to what I expected:

delimiter //
create procedure comissao(idmedico int)
begin
    select nome_medico as 'Médico',
    (select sum(valor * 0.05) from tbl_exames) as 'Valor Total das Comissões'
    from tbl_medico where id_medico = idmedico union
    select tipo as 'Exame', (valor * 0.05) as 'Valor da Comissão' from 
tbl_exames;
end //
delimiter ;

Answer 1

He is saying that the subquery is returning more than 1 value, see its first subquery, it does not have the "sum" so it will return several values

select (valor * 0.05) from tbl_exames

Either you add the sum to it, or rethink your query.

This way should solve your problem:

delimiter //
create procedure comissao(idmedico int)
begin
    select nome_medico as 'Médico',
    (select sum(valor) from tbl_exames) as 'Valor dos Exames',
    (select sum(valor * 0.05) from tbl_exames) as 'Valor Total das Comissões'
    from tbl_medico where id_medico = idmedico;
end //
delimiter ;