Q:

Multiple Sum functions with different Scope on same query

I have a table which says that All students have to take 10 courses, in which 5 are mandatory and choose 3 from other 5. So basically has 2 groups. Now I need to calculate the sum of total Credits and also Sum of group Credits.

Table #1
      StudentID ProgID ProgName GroupID  GroupName  Course Complete_Courses_Alert Credits
          1         100  MS       501      Mandatory  12       Remaining           3  
          1         100  MS       501      Mandatory  13       Complete            3
          1         100  MS       501      Mandatory  14       Complete            3
          1         100  MS       501      Mandatory  15       Remaining           3
          1         100  MS       501      Mandatory  16       Complete            3
          1         100  MS       502      Elective   17       Complete            3
          1         100  MS       502      Elective   18       Complete            3
          1         100  MS       502      Elective   19       Remaining           3
          1         100  MS       502      Elective   20       Complete            3
          1         100  MS       502      Elective   21       Remaining           3

I want the output as 
the above table but with 2 fields more added to it. 
i.e. Sum of Completed Credits(Total Credits)
and Sum Complete Credits(By Group)
This is what I have done so far,
 I have created a view for calculating total credits and GrouptotalCredits and then joinin it on the main query. This way again i need to create another view to execute more functions on the query..
Can anyone help.

A:

I think you're looking for something like:
select *
  , StudentGroupCredits = sum(case when Complete_Courses_Alert = 'Complete' then Credits else 0 end) over (partition by StudentId, GroupName)
  , StudentTotalCredits = sum(case when Complete_Courses_Alert = 'Complete' then Credits else 0 end) over (partition by StudentId)
from Courses

SQL Fiddle with demo.
Check out OVER Clause at Books Online for more information.

