How to....?

Anon

I have a table with 2 colums "total" and "votes". I divide "total" with "votes" and find a score.

I want to find the ten top scores in a table.

Can this be done in a single query and how does it look?

Thanks,
Michael

jburfield

Why not try something like this:

$query = "SELECT (total / votes) as total_votes order by total_votes";

-- Jason

Anon

What if I want to select the entire row AND the result of (total/vote)?

jburfield

$query = "SELECT total, votes, (total / votes) as total_votes order by total_votes";

That gives you both columns plust the divided total.

-- Jason

smarlowe

Here's my way of doing it:

create a table to hold the answers, and one to hold the questions:

create table questions (question text, id serial);
create table answers (answer text, qid int contraint a2q references questions(id) id serial, constraint anspkey primary key (answer,qid);
Then create a table designed to reference those two tables.

create table poll (qid int constraint question_id references questions (id), aid int constraint answer_id references answers (id) , votes int, constraint pkey primary key (qid,aid));

Now you can't create a row in answers that doesn't reference a question, and you can't have the same answer twice for the same question. Further, questions and answers can't be deleted without removing their entries in the poll table.

Assuming we have a single question and four answers, our q and a tables might look like this:
Answers:
answer | qid | id
--------+-----+----
red | 1 | 1
blue | 1 | 2
green | 1 | 4
yellow | 1 | 5

Questions:
question | id
-----------------------------+----
What is your favorite color | 1

And our poll table might look like this:
Poll:
qid | aid | votes
-----+-----+-------
1 | 1 | 20
1 | 2 | 30
1 | 4 | 34
1 | 5 | 47

Then we can get the answers like so:
select aid, votes/(select sum(votes) from poll where qid=1) as score from poll where qid=1 order by 2 desc;

Or,

select (select answer from answers where id=aid), votes/(select sum(votes) from poll where qid=1) as score from poll where qid=1 order by 2 desc;

To get the text answers.

Anon

It works fine but I got some rows with 0 votes and 0 total. How can be that 0/0 is bigger than 50/12? I order with DESC.

Anon

Because anything divided by 0, even 0 divided by 0, is infinity.

You can probably use coalesce or a similar function to force 0 to become -1 or something when there are not votes. Then just turn any negative number into a score of zero on the output.