# 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

Monday, January 28, 2008 1:22 AM

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