CODEWITHSUNDEEP
sqlsql-servert-sql
leilanihagen
leilanihagen

Reputation: 15

Using GROUP BY Inside a CTE for Aggregation

I'm using TSQL and AdventureWorks 2014 to try to compute the SUM of all the LineTotals for each unique order, specified by SalesOrderID. I'm joining on the SalesOrderHead.SalesOrderID because in SalesOrderHeader, SalesOrderID is the primary key. This query takes forever to run, and returns duplicates of the SalesOrderIDs, when there sould only be one record returned for each SalesOrderID. Hope that makes sense...

What I'm trying to do is calculate the SUM(LineTotal) grouped by SalesOrderID inside the SumPerOrderCTE, and then join this to SalesOrderHeader to select some additional tables, and only select from SumPerOrder the records who have the SalesOrderID matching SalesOrderHeader.

Any suggestions? I can't seem to figure out why it's not doing what it should..

Here's the output (notice the SalesOrderID's are all the same, and th differences should mostly be 0 but are not): buggy_output

Small sample of how the output should generally look: desired_output

CREATE OR ALTER PROC [dbo].[SalesOrderDataReviewCTE]
AS
WITH SumPerOrder (SalesOrderID, CalculatedSubTotalFromDetail)
AS
(
SELECT
    SalesOrderID
    ,SUM(LineTotal)
FROM AdventureWorks2014.Sales.SalesOrderDetail
GROUP BY SalesOrderID
)
SELECT
    soh.SalesOrderID
    ,soh.SalesOrderNumber
    ,soh.SubTotal AS OriginalSubTotal
    ,spo.CalculatedSubTotalFromDetail
    ,(soh.SubTotal - spo.CalculatedSubTotalFromDetail) AS Difference
FROM AdventureWorks2014.Sales.SalesOrderHeader soh
    INNER JOIN SumPerOrder spo
        ON soh.SalesOrderID = spo.SalesOrderID

Upvotes: 1

Views: 17209

Answers (2)

George Menoutis
George Menoutis

Reputation: 7240

Why use the CTE?

The CTE is just a handy and one-query way to create a temp table with repeatable code. When you do this, the SumPerOrder table is missing all the indexes of SalesOrderDetail. Thus, the join with SalesOrderHeader takes much longer than what a direct join of SalesOrderHeader with SalesOrderDetail would do.

The simpler way would be:

SELECT
    soh.SalesOrderID
    ,soh.SalesOrderNumber
    ,soh.SubTotal AS OriginalSubTotal
    ,spo.SUM(LineTotal) as CalculatedSubTotalFromDetail
    ,(soh.SubTotal - spo.SUM(LineTotal)) AS Difference
FROM AdventureWorks2014.Sales.SalesOrderHeader soh
    INNER JOIN AdventureWorks2014.Sales.SalesOrderDetail spo
        ON soh.SalesOrderID = spo.SalesOrderID
GROUP BY spo.SalesOrderID,soh.SalesOrderNumber,soh.SubTotal

Upvotes: 1

sacse
sacse

Reputation: 3744

Your query seems to be working fine:

enter image description here

Please check for some other issue.

Upvotes: 0

Related Questions