Re: [PHP-DB] ? Sort by a summation of columns ? From: Noah Spitzer-Williams (noahsw <email protected>)
Date: 11/25/00

Great! But now it goes a little farther...

i have it so my sql statement is:

"SELECT (1*v1+2*v2+3*v3+4*v4+5*v5)/(v1+v2+v3+v4+v5) as sumofv FROM posts
ORDER BY sumofv DESC";

what this does is if they voted a 1, it gives them one point for each vote,
if they voted a 2 its 2 points for each so the votes are weighted.

the problem is, if no one has voted, it tries to divide by zero. however,
sql doesnt give an error, instead it puts these rows ahead of the ones
without votes. so if i was sorting by rating it would look like this:

N/A
N/A
4.0
3.0
2.5
1.0

however i want the N/A's to be at the bottom. got any ideas for that?

- Noah 

--

-----------------
The DevBoard - http://www.thedevboard.com
-----------------
http://www.alladvantage.com/go.asp?refid=BIY971 - AllAdvantage
"Doug Semig" <dougslist <email protected>> wrote in message
news:3.0.6.32.20001125230446.007be320 <email protected>
> You can try your own suggestion, but it won't work.
>
> How about "SELECT v1+v2+v3+v4+v5 AS sumofv FROM ... ORDER BY sumofv"?
>
> By the way, pretend that I wrote about two or three paragraphs about how
> you should make absolutely positive that your table should be designed
that
> way because it's usually not very relational-like to store arrays that
way.
>
> Doug
>
> At 06:43 AM 11/25/00 -0500, Noah Spitzer-Williams wrote:
> >I have a database setup as follows:
> >
> >some fields... | v1 | v2 | v3 | v4 | v5
> >some fields... | v1 | v2 | v3 | v4 | v5
> >some fields... | v1 | v2 | v3 | v4 | v5
> >
> >
> >how can i retrieve a list of these rows sorted by the sum of v1 + v2 + v3
+
> >v4 + v5?
> >
> >for instance, can i do like : ORDER BY sum(v1,v2,v3,v4,v5)?
> >
> >- Noah
> >
> >-- Thanks!
> >
>
>
>
> --
> PHP Database Mailing List (http://www.php.net/)
> To unsubscribe, e-mail: php-db-unsubscribe <email protected>
> For additional commands, e-mail: php-db-help <email protected>
> To contact the list administrators, e-mail: php-list-admin <email protected>
>

-- 
PHP Database Mailing List (http://www.php.net/)
To unsubscribe, e-mail: php-db-unsubscribe <email protected>
For additional commands, e-mail: php-db-help <email protected>
To contact the list administrators, e-mail: php-list-admin <email protected>