RE: [PHP-DB] ? Sort by a summation of columns ? From: David (pinzuti <email protected>)
Date: 11/26/00

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

"Noah Spitzer-Williams" <noahsw <email protected>> escribió en el mensaje de
noticias 8vrd1d$q33$1 <email protected>
> 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>
> 

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