How to get the Sum of all column values in the last row of a resultset?

Scope

As illustrated, ever wonder how to get the sum of column values into the last row as part of the resultset?

Wouldn’t it be nice to let SQL layer to handle all the aggregations and just bind the whole datasource to a .Net control and display as is?



Let’s see how it was done.

Steps

To demonstrate, I have added 2 extra columns to the result set



This was achieved by using GROUPING and ROLLUP (will talk about it at the end).



As you can see, GROUPING indicated in the resultset as if the columns were calculated by the SQL Server and is not part of the normal aggregation result set.

As you may now wonder, if I group another column, it will show as 1 too, because that is the total of everything.



This is the final code of how things were done.

Pay attention to on how ROLLUP is applied to the GROUP BY. Combining HAVING and GROUPING, you will then get the desired sub total or grand total of the column values.

select QuestionId
     , QuestionType
     , QuestionNumber, ISNULL(QuestionDesc, 'Total') as QuestionDesc
     , Question, SUM([Weight]) as [Weight]
     , Grouping(QuestionId) AS GroupingQuestionId
     , Grouping(QuestionType) AS GroupingQuestionType
from (

    -- snipped

) Derived
group by rollup(QuestionId, QuestionNumber, QuestionDesc, Question, [Weight], QuestionType)
having Grouping(QuestionType) = 0 or (Grouping(QuestionId) = 1 and Grouping(QuestionType) = 1 and .........)

Summary

This is a very advanced topic, and my originally query calculates 4 subtotals and 1 grand total. Hence the need of using derived table. If you are only calculating subtotals / totals from 1 table, then you will not use derived table. It was a little difficult to hide the business knowledge versus demonstrating on how to do it, but this select from where group by rollup having grouping will definitely get you a good start.