PROBLEMS WITH "SELECT... AS" HELP

[triphis]

Im trying to return an average from a MySQL query. Here is my current query:

SELECT id,name,points,votes,(points/votes) AS average FROM ratings WHERE id=$id ORDER BY average DESC;

What is the correct way (if any) to to mathematical equations in a query? Thank you for your help!

[DylanBlitz]

I don\'t think you can do mathematics inside of the select. I think you need to pull the data out and do it with the variables.

[triphis]

Could someone give me an example? If I do it with variables, how can I order them BY the average?

[DylanBlitz]

*sigh* I must be tired lol, you can do mathematics, give this a try, just get rid of the ()

 

SELECT id,name,points,votes, points/votes AS average FROM ratings WHERE id=$id ORDER BY average DESC;

[triphis]

Awsome! thank you, it worked.

 

However, I have another question: If, say, for ID #3, votes=0, and points=0, the average will not even show up (you cant divide 0 by 0). For some reason, id #3 is placed ABOVE other IDs with actual averages. Ive tried both ASC, and DESC and the \"empty\" values do not move, only the IDs with actual averages, beneath them move. For example:

DESC

ID Average

3 --- 0

2 --- 0

4 --- 5.2

1 --- 4.7

ASC

ID Average

3 --- 0

2 --- 0

1 --- 4.7

4 --- 5.2

Is there anyway to fix this? This doesn\'t work (because the empty values are NOT 0, they simply do not exist)

SELECT id, name, points, votes, points/votes AS average WHERE average > 0 FROM ratings ORDER BY average DESC LIMIT 10

Any other suggestions?

[Barand]

Filter out those with no votes to avoid the division by zero

 

SELECT id, name, points, votes, points/votes AS average FROM ratings WHERE votes > 0 ORDER BY average DESC LIMIT 10

[DylanBlitz]

Yup, like barand said, exclude the ones that don\'t have any votes, with your code it would be like

 

SELECT id,name,points,votes, points/votes AS average FROM ratings WHERE (id=$id && votes > 0) ORDER BY average DESC;

[triphis]

Awsome! Thank you Barand and DylanBlitz! You have both been incredably helpful! ^^

[DylanBlitz]

no problem, glad we could help out