Post reply

sql sum

  • 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)

  • March 19, 2012 at 5:26 am

    #1461062

    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.

  • March 19, 2012 at 5:30 am

    #1461063

    thanks Pablo (Paul) Berzukov, for ur response

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

  • March 19, 2012 at 5:41 am

    #1461069

    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.

  • March 19, 2012 at 5:49 am

    #1461073

    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

  • March 19, 2012 at 1:11 pm

    #1461311

    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]

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

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