* Mike Podlesny <[EMAIL PROTECTED]> wrote on 19.01.01 17:52:
> Actually let me reword this because Tomi's answer won't quite work. The
> table has a field called Rating. A number 1 is stored to represent positive
> a 2 is stored for neutral and a 3 is stored for negative. I need to query
> that will query up all the number 1's (positives) and subtract all the
> number 3's (negatives) to give me a true rating. Any ideas?
Well, the most straightforward solution would be to first:
select @positives:=count(*) from {whatever-the-table-name-was-again} where rating=1;
then:
select @negatives:=count(*) from {table-name-here-again} where rating=3;
and finally, if you need to do the whole thing in SQL:
select @positives-@negatives;
This not very effective as you need two queries to do this (three if you
actually do need to do it in SQL). If you don't need to do it all in SQL,
you can just drop the "@positives:=" and "@negatives:=" parts from the
queries.
However, with your table structure it is still possible to do:
select sum(rating) from {table-name};
You only need to subtract 2 times the number of rows you received to get the
result you want.
Probably the most efficient way would be to rethink your table format to
have positive ratings as +1, neutrals as 0, and negatives as -1. Then you
could simply sum them all up without needing to subtract anything.
--
Tomi Junnila <[EMAIL PROTECTED]>
http://www.badzilla.net/~topeju/
Electronics and Information Technology,
University of Turku, Finland
---------------------------------------------------------------------
Before posting, please check:
http://www.mysql.com/manual.php (the manual)
http://lists.mysql.com/ (the list archive)
To request this thread, e-mail <[EMAIL PROTECTED]>
To unsubscribe, e-mail <[EMAIL PROTECTED]>
Trouble unsubscribing? Try: http://lists.mysql.com/php/unsubscribe.php
