Hi All,
This blog post is a follow up to a question I received when I gave my Advanced TSQL Webinar for Pragmatic Works. If you haven’t seen that yet and would like to see it you can view the webinar here:
Question: How can we get SubTotals in TSQL using the CTE method you mentioned?
In my webinar I showed how to get totals and sub totals in the same result set as line item detailed information (See screenshot below). The method I used involved using the OVER clause and it kept the SQL very clean and easy to read. Unfortunately this method is not the best performing option available and that is because the over clause without framing uses disk. (I have pasted the TSQL example with the over clause at the bottom of this blog post for comparison and reference purposes.)
Sub Totals in SQL with CTE method:
First of all can I just preface this by saying I love CTEs? (Common Table Expressions). Let’s jump right in and write some code! For this example I’m going to be using the AdventureWorks2012 database but this should work with all versions of Adventure Works.
Step 1) – Create CTE with total information:
WITH TOTALS AS ( SELECT CustomerID, SUM(TotalDue) AS Total FROM Sales.SalesOrderHeader GROUP BY CustomerID ) SELECT * FROM Totals ORDER BY CustomerID
Results:
Step 2: Create an SQL query with line item detail information.
SELECT CustomerID, SalesOrderID, OrderDate, TotalDue FROM Sales.SalesOrderHeader
Step 3: Join them together!
WITH TOTALS AS ( SELECT CustomerID, SUM(TotalDue) AS Total FROM Sales.SalesOrderHeader GROUP BY CustomerID ) SELECT soh.CustomerID, SalesOrderID, OrderDate, TotalDue, Total FROM Sales.SalesOrderHeader soh JOIN Totals t ON t.CustomerID = soh.CustomerID
Final Result:
As I mentioned above you can get the same results using the OVER Clause in TSQL. I have pasted the code below for that example:
SELECT CustomerID, SalesOrderID, OrderDate, TotalDue, SUM(TotalDue) OVER(Partition By CustomerID) AS CustomerTotal FROM Sales.SalesOrderHeader
Final Thoughts: This method will generally perform better than simply using the over clause method, but it takes a more code and work. If the over clause function gets the job done and performance is not an issue I would recommend using that method to keep the code simpler and easier to read!
Thanks for looking!
Mitchellsql 