| Author |
 Topic  |
|
|
hard_life
Starting Member
4 Posts |
 Posted - 02/22/2010 : 14:26:37
|
Hello,
I'm trying to sum the value of a column in a group by, but theres a condition to whether the... rows? should be summed.
Context is that in a simple quiz app i want to rank "quizrounds".. first by the amount of correct answers, next by the lowest possible sum of time spent answering -correct- questions.
Theres a one to many relationship between rounds and answers.
Having a really hard time explaining this which im sure is a great example that i dont really know what im doing.
Anyway so far im at
select roundid ,SUM(timeelapsedseconds) as [time], SUM(CONVERT(int,wascorrect)) as correct
from roundanswer
GROUP BY roundid
order by correct desc, [time] asc
problem with this is that i want the SUM(timeelapsedseconds) to only sum the records that were actually correct. currently it will obviously just sum the timeelapsedseconds regardless of whether the answer was correct or not.
Hope it was clear enough to aid me in any way.
cheers!
|
|
|
Kristen
Test
United Kingdom
22191 Posts |
Posted - 02/22/2010 : 14:54:16
|
| SUM(CASE WHEN AnswerisCorrect = 1 THEN timeelapsedseconds ELSE 0 END) |
 |
|
|
madhivanan
Premature Yak Congratulator
India
22461 Posts |
Posted - 02/23/2010 : 01:19:27
|
or
select roundid ,SUM(timeelapsedseconds) as [time], SUM(CONVERT(int,wascorrect)) as correct
from roundanswer
WHERE AnswerisCorrect =1
GROUP BY roundid
order by correct desc, [time] asc
Madhivanan
Failing to plan is Planning to fail |
|
|
|
visakh16
Very Important crosS Applying yaK Herder
India
47173 Posts |
Posted - 02/23/2010 : 09:37:03
|
quote:
Originally posted by madhivanan
or
select roundid ,SUM(timeelapsedseconds) as [time], SUM(CONVERT(int,wascorrect)) as correct
from roundanswer
WHERE AnswerisCorrect =1
GROUP BY roundid
order by correct desc, [time] asc
Madhivanan
Failing to plan is Planning to fail
if you're appying filter WHERE AnswerisCorrect =1 wont this be enough for getting value of correct?
SUM(1) or COUNT(*)
------------------------------------------------------------------------------------------------------
SQL Server MVP
http://visakhm.blogspot.com/
|
|
|
|
madhivanan
Premature Yak Congratulator
India
22461 Posts |
Posted - 02/23/2010 : 09:39:02
|
quote:
Originally posted by visakh16
quote:
Originally posted by madhivanan
or
select roundid ,SUM(timeelapsedseconds) as [time], SUM(CONVERT(int,wascorrect)) as correct
from roundanswer
WHERE AnswerisCorrect =1
GROUP BY roundid
order by correct desc, [time] asc
Madhivanan
Failing to plan is Planning to fail
if you're appying filter WHERE AnswerisCorrect =1 wont this be enough for getting value of correct?
SUM(1) or COUNT(*)
------------------------------------------------------------------------------------------------------
SQL Server MVP
http://visakhm.blogspot.com/
Yes it is 
Madhivanan
Failing to plan is Planning to fail |
|
|
| |
Topic |
|
sum'ing a column in a group by (newbie)
