#### Archived

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

# 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 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 on other sites

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

##### 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 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 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 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 on other sites

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

##### Share on other sites

no problem, glad we could help out