Problem with subquery

Question

Problem with subquery

Navigation

#1 by (1 votes)

0

Good evening,

I need to bring the complete 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 the error occurs: Error Code: 1242. Subquery returns more than 1 row.

It will list all the values of the exams being 5% of it.

Edit:

I'd like it to look like this:

Doctor - name

Exam value - value1, value2, value3, etc.

Total Commissions Amount - Sum of above values

Edit2:

It would be like this, but close 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 ;

mysql sql

asked by anonymous 26.09.2017 / 04:41

1 answer

Responsive utilities of Bootstrap 4 How to list the last message of all conversations

score 1 · Accepted Answer

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

select (valor * 0.05) from tbl_exames

Or you add the sum on it, or rethink your query.

This 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 ;