CODEWITHSUNDEEP
sqlsql-servert-sql
CuriousDev
CuriousDev

Reputation: 1275

SELECT Query with a Total at the Bottom

I have a table in SQL Server 2012. The following query works great:

SELECT TOP 300 [ObjectID], [tbh_Objects].Title, [Quantity], [tbh_Section].Title
FROM [ECom].[dbo].[tbh_Objects] 
INNER JOIN [tbh_Section] ON tbh_Objects.SectionID = tbh_Section.SectionID 
ORDER BY tbh_Objects.AddedDate DESC

I just want to do a Total at the end of the Quantity column keeping the same query. I read about Group By ROLL UP but it I can't seem to wrap my head around it.

Can anyone help please?

Upvotes: 1

Views: 1198

Answers (2)

SixtusTyrannicus
SixtusTyrannicus

Reputation: 56

The GROUP BY and ROLL UP don't quite work that way I'm afraid.

What you're asking would be trivial in Excel, but to have a total at the end SQL needs to SUM, and to SUM it needs to remove all the duplicate lines that are part of the group, hence the GROUP BY. However this defeats what you want, which is a balance at the end while keeping all the individual lines.

Something like the below will work without window functions, just to give you the idea. However it won't scale very well so this is more along the lines of getting a quick fix. Even for this it could do with another column to ensure the total always goes to the bottom. The bigger this gets, either by number of rows or number of subtotals, the sooner I'd suggest moving to window functions and having the various totals unioned in at the end or displayed by application/report.

WITH cteIndividualLines AS
(
    SELECT TOP 300 [ObjectID], [tbh_Objects].Title, [Quantity], [tbh_Section].Title
    FROM [ECom].[dbo].[tbh_Objects] 
    INNER JOIN [tbh_Section] ON tbh_Objects.SectionID = tbh_Section.SectionID 
    ORDER BY tbh_Objects.AddedDate DESC
),
cteGrandTotal AS
(
    SELECT
        GrandTotal = SUM(Quantity)
    FROM
        cteIndividualLines
)
SELECT
     ObjectID
    ,Title
    ,Quantity
    ,Title
FROM
    cteIndividualLines


UNION ALL


SELECT
     NULL
    ,'Grand Total'
    ,GrandTotal
    ,NULL
FROM
     cteGrandTotal

Upvotes: 2

Ross Bush
Ross Bush

Reputation: 15185

This should work -> Sum with your detail query using a window function for the roll-up.

SELECT TOP 300 [ObjectID], [tbh_Objects].Title, [Quantity], [tbh_Section].Title,
SumQuantity = SUM([Quantity]) OVER (PARTITION BY ObjectID)   
FROM [ECom].[dbo].[tbh_Objects] 
INNER JOIN [tbh_Section] ON tbh_Objects.SectionID = tbh_Section.SectionID 
ORDER BY tbh_Objects.AddedDate DESC

If you need to keep the values aligned with what is in the TOP then this query should work out.

;WITH A AS
(
    SELECT [ObjectID], [tbh_Objects].Title, [Quantity], [tbh_Section].Title, 
    TopValue= ROW_NUMBER() OVER(ORDER BY tbh_Objects.AddedDate)
    FROM [ECom].[dbo].[tbh_Objects] 
    INNER JOIN [tbh_Section] ON tbh_Objects.SectionID = tbh_Section.SectionID
 ) 
 SELECT 
     *,
      SumQuantity = SUM([Quantity]) OVER (PARTITION BY ObjectID) 
 FROM
     A
WHERE
    TopValue <= 300

Upvotes: 0

Related Questions