Running Totals

[mimi2]

Hello,
This is the first time I am trying to run sUb-totals and i am having some difficulty to do it.

the table i created is like this:

user answer user_answer
---------------------------
usr1 AG AG
usr1 AG AS
usr2 AG AS
usr2 HS AS

i am trying to count the number of correct answers given by a user.

I would like to have this:

user answer user_answer
-------------------------
usr1 AG AG (--->correct )
usr1 AG AG (--->correct)
usr1 AG AS (--->incorrect)
Total 2 correct , 1 incorrect

usr2 AG AS (--->incorrect)
usr2 HS AS (--->incorrect)
total 0 correct , 2 incorrect

Actually, i would love to add % of correct and incorrect answers.

Any help is greatly appreciated.

 

[mwolf00]

SELECT user, sum(case user_answer when 'AG' then 1 else 0 END) as correct, sum(case user_answer when 'AS' then 1 else 0 END) as incorrect FROM myTable group by user

Programming today is a race between software engineers striving to build better and bigger idiot-proof programs, and the Universe trying to produce bigger and better idiots. So far, the Universe is winning. - Rick Cook (No, I'm not Rick)

 

[zhavic]

and with %:

SELECT [user],
SUM( CASE WHEN answer = user_answer THEN 1 ELSE 0 END ) AS correct,
SUM( CASE WHEN answer != user_answer THEN 1 ELSE 0 END ) AS incorrect,
AVG( CASE WHEN answer = user_answer THEN 1.0 ELSE 0.0 END ) * 100 AS percentage_correct,
AVG( CASE WHEN answer != user_answer THEN 1.0 ELSE 0.0 END ) * 100 AS percentage_incorrect
FROM your_table
GROUP BY [user]

Zhavic




---------------------------------------------------------------
In the 1960s you needed the power of two Comodore64s to get a rocket to the moon. Now you need a machine which is a vast number of times more powerful just to run the most popular GUI.

 

[mwolf00]

DOH!! - user_answer should equal answer - DOH!!!

I didn't see that - all the correct answers were 'AG'



Programming today is a race between software engineers striving to build better and bigger idiot-proof programs, and the Universe trying to produce bigger and better idiots. So far, the Universe is winning. - Rick Cook (No, I'm not Rick)

 

[mimi2]

thanks a lot for your help.

SELECT [user],
SUM( CASE WHEN answer = user_answer THEN 1 ELSE 0 END ) AS correct,

did the job BUT (there is always a "but&quotin the example i gave, usr1 should have 33% incorrect and user2 100%.

this piece of code gives either a 0% or a 100% (per row):
AVG( CASE WHEN answer = user_answer THEN 1.0 ELSE 0.0 END ) * 100 AS percentage_correct,

I am hoping to have one percentage per user (and not per row).Meaning number of incorrect answers / total answers for each user (total =3 for usr1 and total = 2 for usr2).

Thanks again for helping me use new tools.

 

[mwolf00]

mimi -

I'm not following your problem. I've used the code as you've posted it, and I've gotten the exact results you want.

user1 66.666600 33.333300
user2 .0000000 100.00000

what are you getting?



Programming today is a race between software engineers striving to build better and bigger idiot-proof programs, and the Universe trying to produce bigger and better idiots. So far, the Universe is winning. - Rick Cook (No, I'm not Rick)

 

[mimi2]

My mistake.

I added other fields in the group by part.
It is working now (that i group by user only)

thanks a lot again