SQL Server Forums
Profile | Register | Active Topics | Members | Search | Forum FAQ
 
Register Now and get your question answered!
Username:
Password:
Save Password
Forgot your Password?

 All Forums
 General SQL Server Forums
 New to SQL Server Programming
 sum'ing a column in a group by (newbie)
 New Topic  Reply to Topic
 Printer Friendly
Author Previous Topic Topic Next Topic  

hard_life
Starting Member

4 Posts

Posted - 02/22/2010 :  14:26:37  Show Profile  Reply with Quote
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
22431 Posts

Posted - 02/22/2010 :  14:54:16  Show Profile  Reply with Quote
SUM(CASE WHEN AnswerisCorrect = 1 THEN timeelapsedseconds ELSE 0 END)
Go to Top of Page

madhivanan
Premature Yak Congratulator

India
22769 Posts

Posted - 02/23/2010 :  01:19:27  Show Profile  Send madhivanan a Yahoo! Message  Reply with Quote
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
Go to Top of Page

visakh16
Very Important crosS Applying yaK Herder

India
52325 Posts

Posted - 02/23/2010 :  09:37:03  Show Profile  Reply with Quote
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/

Go to Top of Page

madhivanan
Premature Yak Congratulator

India
22769 Posts

Posted - 02/23/2010 :  09:39:02  Show Profile  Send madhivanan a Yahoo! Message  Reply with Quote
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
Go to Top of Page
  Previous Topic Topic Next Topic  
 New Topic  Reply to Topic
 Printer Friendly
Jump To:
SQL Server Forums © 2000-2009 SQLTeam Publishing, LLC Go To Top Of Page
This page was generated in 0.56 seconds. Powered By: Snitz Forums 2000