# PROBLEMS WITH "SELECT... AS" HELP

8 replies to this topic

### #1 triphis

triphis
• Members
• 62 posts

Posted 19 August 2003 - 04:46 PM

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!
Xac Attack

### #2 DylanBlitz

DylanBlitz
• Members
• 99 posts
• LocationOC Baby!

Posted 19 August 2003 - 05:16 PM

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.

### #3 triphis

triphis
• Members
• 62 posts

Posted 19 August 2003 - 05:21 PM

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

### #4 DylanBlitz

DylanBlitz
• Members
• 99 posts
• LocationOC Baby!

Posted 19 August 2003 - 05:29 PM

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

### #5 triphis

triphis
• Members
• 62 posts

Posted 19 August 2003 - 05:57 PM

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:
[quote]
DESC
ID Average
3 --- 0
2 --- 0
4 --- 5.2
1 --- 4.7
[/quote]
[quote]
ASC
ID Average
3 --- 0
2 --- 0
1 --- 4.7
4 --- 5.2
[/quote]
Is there anyway to fix this? This doesn\'t work (because the empty values are NOT 0, they simply do not exist) [quote]
SELECT id, name, points, votes, points/votes AS average WHERE average > 0 FROM ratings ORDER BY average DESC LIMIT 10[/quote]Any other suggestions?[/quote]
Xac Attack

### #6 Barand

Barand
• Moderators
• Sen . ( ile || sei )
• 18,021 posts

Posted 19 August 2003 - 07:22 PM

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`

If you are still using mysql_ functions, STOP! Use mysqli_ or PDO. The longer you leave it the more you will have to rewrite.

 |baaGrid| easy data tables - and more|baaChart| easy line, column and pie charts

### #7 DylanBlitz

DylanBlitz
• Members
• 99 posts
• LocationOC Baby!

Posted 19 August 2003 - 07:29 PM

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

### #8 triphis

triphis
• Members
• 62 posts

Posted 20 August 2003 - 02:52 AM

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

DylanBlitz
• Members