sql sum

Question

sql sum

shanish

Right there with Babe

Points: 768
More actions
March 19, 2012 at 5:20 am

#257973

Hi everybody, I have a doubt here,
I have a table contains the columns StudentId,SubjectId,ComponentId,Marks,PartNo
I have a record for a student,
1,1,1,40,1 // here he scored 40 in part one
1,1,1,30,2 //here he scored 30 in part two
what I need is I need to sum the two marks(40+30) and I need to check that the sum value is greater than the minmark(which I set in another table), if it greater than that then it is "Pass" else it is "Fail" how can I do this
//note : I declared the type of Marks as varchar(just to get the grade as well)

Viewing 6 posts - 1 through 5 (of 5 total)

You must be logged in to reply to this topic. Login to reply

PaulB-TheOneAndOnly SSC Guru Points: 66772 More actions · Answer 1

shanish (3/19/2012)
Hi everybody, I have a doubt here,
I have a table contains the columns StudentId,SubjectId,ComponentId,Marks,PartNo
I have a record for a student,
1,1,1,40,1 // here he scored 40 in part one
1,1,1,30,2 //here he scored 30 in part two
what I need is I need to sum the two marks(40+30) and I need to check that the sum value is greater than the minmark(which I set in another table), if it greater than that then it is "Pass" else it is "Fail" how can I do this

Research GROUP BY, SUM() and HAVING

_____________________________________
Pablo (Paul) Berzukov

Author of Understanding Database Administration available at Amazon and other bookstores.

Disclaimer: Advice is provided to the best of my knowledge but no implicit or explicit warranties are provided. Since the advisor explicitly encourages testing any and all suggestions on a test non-production environment advisor should not held liable or responsible for any actions taken based on the given advice.

shanish Right there with Babe Points: 768 More actions · Answer 2

thanks Pablo (Paul) Berzukov, for ur response

of course am doing, I need some idea, how to do that,thats wat askin here

PaulB-TheOneAndOnly SSC Guru Points: 66772 More actions · Answer 3

shanish (3/19/2012)
thanks Pablo (Paul) Berzukov, for ur response
of course am doing, I need some idea, how to do that,thats wat askin here

Okay... for starters, please show us how you do a GROUP BY by (StudentId,SubjectId,ComponentId) while doing a SUM() of Mark.

_____________________________________
Pablo (Paul) Berzukov

Author of Understanding Database Administration available at Amazon and other bookstores.

Disclaimer: Advice is provided to the best of my knowledge but no implicit or explicit warranties are provided. Since the advisor explicitly encourages testing any and all suggestions on a test non-production environment advisor should not held liable or responsible for any actions taken based on the given advice.

shanish Right there with Babe Points: 768 More actions · Answer 4

declare @minmarks int

select @minmarks=minmarks from table2

select (case when sum(convert(int,marks)) >= @minmarks then 'Pass' else 'Fail' end) as Result

from table1

GROUP BY StudentId,SubjectId,ComponentId

am getting the result, thanks

CapnHector SSCoach Points: 16143 More actions · Answer 5

shanish (3/19/2012)
declare @minmarks int
select @minmarks=minmarks from table2
select (case when sum(convert(int,marks)) >= @minmarks then 'Pass' else 'Fail' end) as Result
from table1
GROUP BY StudentId,SubjectId,ComponentId
am getting the result, thanks

does the minimum for pass/fail change depending on the subjectID or ComponentID? if so you would need to rewrite your query a bit. if not you could hard code the minimum into your query and get rid of the variable.

if the minmarks changes please provide table and data DDL examples.

Thanks

For faster help in answering any problems Please read How to post data/code on a forum to get the best help - Jeff Moden[/url] for the best way to ask your question.

For performance Issues see how we like them posted here: How to Post Performance Problems - Gail Shaw[/url]

Need to Split some strings? Jeff Moden's DelimitedSplit8K[/url]
Jeff Moden's Cross tab and Pivots Part 1[/url]
Jeff Moden's Cross tab and Pivots Part 2[/url]