Jump to content

Archived

This topic is now archived and is closed to further replies.

triphis

PROBLEMS WITH "SELECT... AS" HELP

Recommended Posts

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!

Share this post


Link to post
Share on other sites

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.

Share this post


Link to post
Share on other sites

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

Share this post


Link to post
Share on other sites

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

Share this post


Link to post
Share on other sites

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?

Share this post


Link to post
Share on other sites

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

Share this post


Link to post
Share on other sites

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;

Share this post


Link to post
Share on other sites

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

Share this post


Link to post
Share on other sites

×

Important Information

We have placed cookies on your device to help make this website better. You can adjust your cookie settings, otherwise we'll assume you're okay to continue.