Please start any new threads on our new
site at https://forums.sqlteam.com. We've got lots of great SQL Server
experts to answer whatever question you can come up with.
| Author |
Topic |
|
elomon
Starting Member
37 Posts |
 Posted - 2002-03-12 : 10:51:31
|
| I have a system that asks a question and stores whether the user answered correctly. I want to display the user's name, answer for this question and percent correct for all exisiting questions.Two tables, tblUser,tblAnswer. TblUser: user_id INT user_name VarChartblAnswer: user_id INT (FK) ques_id INT (FK) ans(BIT)If I want to get the username & their answer for a particluar question:select U.user_name,A.ans from tblAnswer A,tblUser U where A.ques_ID=xx and U.user_id=A.user_IDwhere xx is a question number. But now I need also pullthe percent correct for *all* questions. I can do this:select U.user_name,A.ans,(select count(user_id) form tblAnswer where user_id=A.user_id and ans=1) as correct,(select count(user_id) from tblAnswer where user_id=A.user_ID) as numAnswers ....which will give me the user name,answer for this question and total correct,total answered. Percent correct=correct/answered.Ok, you say, what's the problem? Well, I need to be able to Order by percent correct. Right now when I write the info back to the user I just do a little division, but they want to be able to sort by percent correct.Any ideas? I'm willing to pull the percent correct into it's own query if necessary, I just can't figure out how to divide the resultas before outputting them. Thanks for any help. |
|
|
joldham
Wiseass Yak Posting Master
300 Posts |
Posted - 2002-03-12 : 12:28:46
|
| Try ORDER BY correct/answered and let me if this worked.Probably a better way is ORDER BY correct/IsNull(answered,1) would be better, just in case they have not answered the question. This keeps the dreaded Divide by Zero error from rearing it's ugly head.JeremyEdited by - joldham on 03/12/2002 12:31:02 |
 |
|
|
elomon
Starting Member
37 Posts |
Posted - 2002-03-12 : 12:40:14
|
| I tried but got 'invalid column name' for correct & answered. Maybe it's easier to say all I can't figure out is *how* to divide the two calculated items:Let me boil this to the simplest:tblAnswer:user_id INTanswer bit (0=incorrect,1=correct)let's say i only want to figure out where answer=1 divided by total number of answer:I can't:select user_id,count(answer=0)/count(answer) or anything like that? |
|
|
|
joldham
Wiseass Yak Posting Master
300 Posts |
Posted - 2002-03-13 : 08:12:45
|
| elomon,Can you post your SQL statement that you are trying to run, as well as the exact columns names in the table? If you can post some code, I will look into this further.Jeremy |
|
|
|
elomon
Starting Member
37 Posts |
Posted - 2002-03-13 : 10:12:00
|
| Thanks for the offer, I managed to figure it out. Problem was trying to get percentages by dividing two Int, I CAST them to Float & all's well. |
|
|
|
|
|
|
|
|
Help with report please? (long)