Jump to content


Photo

PROBLEMS WITH "SELECT... AS" HELP


  • Please log in to reply
8 replies to this topic

#1 triphis

triphis
  • Members
  • PipPipPip
  • Advanced Member
  • 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
  • PipPipPip
  • Advanced Member
  • 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
  • PipPipPip
  • Advanced Member
  • 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
  • PipPipPip
  • Advanced Member
  • 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
  • PipPipPip
  • Advanced Member
  • 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.

Donations gratefully received






moon.png

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

#7 DylanBlitz

DylanBlitz
  • Members
  • PipPipPip
  • Advanced Member
  • 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
  • PipPipPip
  • Advanced Member
  • 62 posts

Posted 20 August 2003 - 02:52 AM

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

#9 DylanBlitz

DylanBlitz
  • Members
  • PipPipPip
  • Advanced Member
  • 99 posts
  • LocationOC Baby!

Posted 20 August 2003 - 04:19 AM

no problem, glad we could help out :)




0 user(s) are reading this topic

0 members, 0 guests, 0 anonymous users