# Some help with fun SQL stuff **URL:** \ **Category:** Rails\ **Created:** [June 13, 2006, 1:56am UTC](https://www.ruby-forum.com/t/some-help-with-fun-sql-stuff/95766 "2006-06-13T01:56:25Z")\ **Posts on this page:** 5\ **Page:** 1
**Author:** ![pergesu](https://www.ruby-forum.com/letter_avatar_proxy/v4/letter/p/22d042/32.png) [@pergesu](https://www.ruby-forum.com/u/pergesu)\ **Post date:** [June 13, 2006, 1:56am UTC](https://www.ruby-forum.com/t/some-help-with-fun-sql-stuff/95766/1 "2006-06-13T01:56:25Z")
I have one table that stores data related to math problems, and another table that stores users’ answers to those problems. To make this very simple, we’ll say that the math table has two numbers that get subtracted, and the answers table has a field that can either be “pos” or “neg” math * * * 2 5 8 3 Okay so if we do the math, we know that the correct answer to record 1 (2 -5) is “neg”, and the correct answer to record 2 (8-3) is “pos”. What I’d like to do is get the user’s answers to these questions, and determine what % they get correct. I really want to do this in as few queries as possible though. Right now I think the easiest way is to just change the table a bit so it keeps the correct answer. Then I can do two queries: SELECT COUNT(\*) FROM answers WHERE user\_id=5; SELECT COUNT(SELECT answer FROM answers, problems WHERE answers.answer=problems.answer AND answers.user\_id=5)); The first one gets the number of total questions answered, the second one gets the number of ones correctly answered. The first query - the second query gives me incorrect answers. Also I’m not entirely sure that the syntax for #2 is valid, I think it is though. First of all, is there a fast way that I can do this without having to create another field representing the answer? I’m kind of thinking not…I’d be performing these queries a lot, makes sense just to keep the answer in the db so I can index it, rather than calculating it every time. Secondly, can I do this with AR? Don’t know if it can handle subqueries. Pat ---
**Author:** ![pergesu](https://www.ruby-forum.com/letter_avatar_proxy/v4/letter/p/22d042/32.png) [@pergesu](https://www.ruby-forum.com/u/pergesu)\ **Post date:** [June 13, 2006, 4:28am UTC](https://www.ruby-forum.com/t/some-help-with-fun-sql-stuff/95766/2 "2006-06-13T04:28:41Z")
On 6/12/06, Glenn C. [removed\_email\_address@domain.invalid](mailto:removed_email_address@domain.invalid) wrote: > > 8 3 > > SELECT COUNT(\*) FROM answers WHERE user\_id=5; > > (neg) field. Could you explain a little more? > Okay let’s say my app shows (4 - 2) on the page. The users of the app > don’t have to figure out the answer, but simply tell me if the result > is positive or negative. Pat ---
**Author:** ![pergesu](https://www.ruby-forum.com/letter_avatar_proxy/v4/letter/p/22d042/32.png) [@pergesu](https://www.ruby-forum.com/u/pergesu)\ **Post date:** [June 13, 2006, 3:40am UTC](https://www.ruby-forum.com/t/some-help-with-fun-sql-stuff/95766/3 "2006-06-13T03:40:38Z")
Pat M. wrote: > I have one table that stores data related to math problems, and > another table that stores users’ answers to those problems. To make > this very simple, we’ll say that the math table has two numbers that > get subtracted, and the answers table has a field that can either be > “pos” or “neg” > > math > > * * * > > 2 5 > 8 3 > > Okay so if we do the math, we know that the correct answer to record 1 > (2 -5) is “neg”, and the correct answer to record 2 (8-3) is “pos”. > What I’d like to do is get the user’s answers to these questions, and > determine what % they get correct. I really want to do this in as few > queries as possible though. > > Right now I think the easiest way is to just change the table a bit so > it keeps the correct answer. Then I can do two queries: > > SELECT COUNT(\*) FROM answers WHERE user\_id=5; > > SELECT COUNT(SELECT answer FROM answers, problems WHERE > answers.answer=problems.answer AND answers.user\_id=5)); > > The first one gets the number of total questions answered, the second > one gets the number of ones correctly answered. The first query - the > second query gives me incorrect answers. > > Also I’m not entirely sure that the syntax for #2 is valid, I think it > is though. > > First of all, is there a fast way that I can do this without having to > create another field representing the answer? I’m kind of thinking > not…I’d be performing these queries a lot, makes sense just to keep > the answer in the db so I can index it, rather than calculating it > every time. Secondly, can I do this with AR? Don’t know if it can > handle subqueries. > > Pat Sorry this is a little confusing particularly about having a (pos) or (neg) field. Could you explain a little more? If we keep the answer in the problem table then I would try something like this. correctly\_answered = Answers.find\_by\_sql ( “select count(a._) as total\_answers, count(p._) as correctly\_answered " + " from answers as a left outer join problems as p on \* a.problem\_id = p.id )” + " where a.answer = p.answer " + " and a.user\_id = ’ + user\_id ) if a computed answer is needed then correctly\_answered = Answers.find\_by\_sql ( “select count(a._) as total\_answers, count(p._) as correctly\_answered " + " from answers as a left outer join problems as p on \* a.problem\_id = p.id )” + " where (a.number\_to\_subtract\_from - a.number\_for\_the\_amount\_to\_subtract) = p.answer " + " and a.user\_id = ’ + user\_id ) ---
**Author:** ![pergesu](https://www.ruby-forum.com/letter_avatar_proxy/v4/letter/p/22d042/32.png) [@pergesu](https://www.ruby-forum.com/u/pergesu)\ **Post date:** [June 13, 2006, 5:07am UTC](https://www.ruby-forum.com/t/some-help-with-fun-sql-stuff/95766/4 "2006-06-13T05:07:16Z")
correctly\_answered = Answers.find\_by\_sql ( “select count(a._) as total\_answers, count(p._) as correctly\_answered " + " from answers as a left outer join problems as p on \* a.problem\_id = p.id )” + " where sign(a.number\_to\_subtract\_from - a.number\_for\_the\_amount\_to\_subtract) = sign(p.answer) " + " and a.user\_id = ’ + user\_id ) ---
**Author:** ![pergesu](https://www.ruby-forum.com/letter_avatar_proxy/v4/letter/p/22d042/32.png) [@pergesu](https://www.ruby-forum.com/u/pergesu)\ **Post date:** [June 13, 2006, 5:05am UTC](https://www.ruby-forum.com/t/some-help-with-fun-sql-stuff/95766/5 "2006-06-13T05:05:19Z")
Pat M. wrote: > On 6/12/06, Glenn C. [removed\_email\_address@domain.invalid](mailto:removed_email_address@domain.invalid) wrote: > > > > 8 3 > > > SELECT COUNT(\*) FROM answers WHERE user\_id=5; > > > > (neg) field. Could you explain a little more? > > Okay let’s say my app shows (4 - 2) on the page. The users of the app > > don’t have to figure out the answer, but simply tell me if the result > > is positive or negative. > > Pat Assuming you are talking about kids students then in postgresql (i have no idea about mysql) then you could try the “sign” function sign(question.from\_number - question.subtract\_by) = sign(answer.answer\_value) Where answer.answer\_value is 1,-1 (is zero possible?) check\_box(“answer”, “answer\_value”, { }, “-1”, “1” )