sum'ing a column in a group by (newbie)

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.

All Forums

General SQL Server Forums

New to SQL Server Programming

sum'ing a column in a group by (newbie)

Author

Topic

hard_life
Starting Member

4 Posts

Posted - 2010-02-22 : 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

22859 Posts

Posted - 2010-02-22 : 14:54:16

SUM(CASE WHEN AnswerisCorrect = 1 THEN timeelapsedseconds ELSE 0 END)

madhivanan
Premature Yak Congratulator

22864 Posts

Posted - 2010-02-23 : 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

52326 Posts

Posted - 2010-02-23 : 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

22864 Posts

Posted - 2010-02-23 : 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

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources