P_Ferreira - 4 months ago 36

MySQL Question

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

begin

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

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.