P_Ferreira P_Ferreira - 1 year ago 70
MySQL Question

Different results for the same query but inside a function

I have a table with Pontuation(Pontuacao) and an unique number for Accomodation(Estadia) and i want to calculate the average pontuation of each accomodation.
This is the table:

Estadia | Pontuacao
5 | 5
5 | 5

So i made this funcion:

delimiter $$
create function mediapontuacao(estadia int)
returns float
declare media float;
select sum(Pontuacao)/count(*) into media
from EstadiaUtilizador
where Estadia = estadia;
return media;
end $$

If i do this

select mediapontuacao(5); //calculate average pontuation of the accomodation which number is 5

This query gives me the value of 3.965.
But if i do this

select sum(Pontuacao)/count(*)
from EstadiaUtilizador
where Estadia = 5;

In other words calculate average pontuation of the accomodation which number is 5, the exact same thing the function i wrote should do and this query gives me the value of 5.00 which is the correct answer.
I am puzzled why i get different values when it should give the same value, i think.

Answer Source

The problem is here:

where Estadia = estadia

which is the same as, say,

where 1 = 1

Your parameter and column should have different names, so the DBMS knows what you are talking about.

Recommended from our users: Dynamic Network Monitoring from WhatsUp Gold from IPSwitch. Free Download