Re: [PHP-DB] ? Sort by a summation of columns ? From: Bob Hall (bobhall <email protected>)
Date: 11/26/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?

Sir, try
    SELECT (1*v1+2*v2+3*v3+4*v4+5*v5)/If((v1+v2+v3+v4+v5), (v1+v2+v3+v4+v5), 1)

Bob Hall

Know thyself? Absurd direction!
Bubbles bear no introspection. -Khushhal Khan Khatak 

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