How to get Line Item Detail information with Totals in T-SQL (Percent of Parent).

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.)

image

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:

image

Step 2: Create an SQL query with line item detail information.

SELECT CustomerID, SalesOrderID, OrderDate, TotalDue
FROM Sales.SalesOrderHeader

image

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:

image

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!

Advertisements

2 thoughts on “How to get Line Item Detail information with Totals in T-SQL (Percent of Parent).

  1. Dustin Ryan July 1, 2016 / 3:28 pm

    Hey, buddy. Another easy way to produce subtotals in your TSQL select statement is to use grouping sets with the GROUP BY GROUPING SETS function. So you could do something like the follow to rollup your data to a specific level:

    SELECT s.SalesTerritoryRegion,
    s.SalesTerritoryGroup,
    SUM(f.SalesAmount) TotalSales

    FROM dbo.FactResellerSales f
    INNER JOIN dbo.DimSalesTerritory s
    ON f.SalesTerritoryKey = s.SalesTerritoryKey

    GROUP BY GROUPING SETS (
    s.SalesTerritoryRegion,
    s.SalesTerritoryGroup,
    (s.SalesTerritoryRegion,s.SalesTerritoryGroup)
    )

    If you run that against Adventure Works DW, you’ll see that it’ll produce subtotals for the SalesTerritoryGroup field and SalesTerritoryRegion. You can read more about here: https://msdn.microsoft.com/en-us/library/ms177673.aspx

Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out / Change )

Twitter picture

You are commenting using your Twitter account. Log Out / Change )

Facebook photo

You are commenting using your Facebook account. Log Out / Change )

Google+ photo

You are commenting using your Google+ account. Log Out / Change )

Connecting to %s