none
Subtotals within a select statement

    Question

  • For the past two hours I've been looking for a solution to my problem. Any help is appreciated. Basically, I need to know the T-SQL Command(s) that create subtotals and grandtotals within a set of results. I am designing a school transcript generator and I need to calculate the GPA and Credit Hour Totals per Semester, For Example:

     

    Semester 1 Record

    Semester 1 Record

    Semester 1 Record

    Semester 1 Record

    Semester 1 Record

    Semester 1 Subtotal

    Semester 2 Record

    Semester 2 Record

    Semester 2 Record

    Semester 2 Record

    Semester 2 Record

    Semester 2 Subtotal

    Semester 3 Record

    Semester 3 Record

    Semester 3 Record

    Semester 3 Record

    Semester 3 Record

    Semester 3 Subtotal

    Year 1 Grand Total

     

     

    Thanks for your help in advance...

    Monday, January 28, 2008 1:22 AM

Answers

  • How about this?

     

    Code Snippet

    DECLARE @t TABLE (Semester VARCHAR(10), Subject VARCHAR(10), Mark INT)

    INSERT INTO @t(Semester, Subject, Mark)

    SELECT 'semester1', 'subject1', 20 UNION ALL

    SELECT 'semester1', 'subject2', 30 UNION ALL

    SELECT 'semester1', 'subject3', 12 UNION ALL

    SELECT 'semester1', 'subject4', 40 UNION ALL

    SELECT 'semester2', 'subject1', 10 UNION ALL

    SELECT 'semester2', 'subject2', 40 UNION ALL

    SELECT 'semester2', 'subject3', 62 UNION ALL

    SELECT 'semester2', 'subject4', 40

     

     

    SELECT Semester, Mark FROM (

    SELECT 0 AS sort1, Semester, Mark, 0 AS sort2 FROM @t

    UNION ALL

    SELECT 0, Semester + ' TOTAL', SUM(mark), 1 FROM @t

    GROUP BY Semester + ' TOTAL'

    UNION ALL

    SELECT 1, 'Grand Total', SUM(mark), 1 FROM @t

    ) a

    ORDER BY Sort1, Semester, Sort2

    /*

    OUTPUT:

    Semester Mark

    ---------------- -----------

    semester1 20

    semester1 30

    semester1 12

    semester1 40

    semester1 TOTAL 102

    semester2 10

    semester2 40

    semester2 62

    semester2 40

    semester2 TOTAL 152

    Grand Total 254

    */

     

     

    Monday, January 28, 2008 2:40 AM
    Moderator