Selecting and comparing from multiple rows

jasonyoung

HI everybody,

What I'm trying to do is search a single table and extract the relevant data for each user, across multiple rows. Here's how the table looks:

uid | date | correctAnswer | user1Answer | user2Answer...
1 | 2007-03-24 | 1 | 3 | 1
2 | 2007-02-28 | 3 | 1 | 1

This format carries forward as time goes on. It's a simple quiz storing table where every week the boss asks us all a question and we have to answer. They are all multiple choice numbered 1 thru 4.

In my sample table above, on 3-24-07 the correct answer was 1, and only user2 got it right. On 2-28, the correct answer was 3, and nobody got it right.

Make sense?? OK. Here's where I get stuck. I need to write a query to search the table and count how many times the user was correct AND incorrect. I'll add the two together and divide to get the percent correct. The resulting is all GUI stuff which I'm fine with. The reason I need to get both is because sometimes we aren't all around the office to take the quiz, so I can't mathematically include every instance in the table for these calculations or the percentages would be inaccurate.

I can't get it to move to the next row. ARRG! If my SELECT statement is qualified with a WHERE clause, I get the row matching the clause. If I remove the clause, I get nothing.

Thanks in advance!

wilku

I'd think over the database structure. What you'll do if there will be more users? You'll have to change your structure every time a new user joins.
It'd be better to create separate table for quizes (to store id, date and correct answer) and another table for storing answers (with id to link to the first table, username, and user answer).

Anyway - to count the number of rows you can use COUNT() in sql.

SELECT COUNT(*) FROM table WHERE user1Answer=correctAnswer

That was for yor table structure for user1.
When you apply my data structure, you can join the tables (using JOIN) use COUNT and GROUP BY to retrieve results for all users with a single query.