how to show your total at the bottom?

_{Wednesday, July 30, 2008 6:05 PM}

I need to show my total at the bottom which I believe can be done with 'GROUP BY WITH ROLLUP'

and this will execute...BUT i am getting repeating rows

SELECT T0.[DocNum],  SUM(T1.[LineTotal]) as 'Price with Total',  T0.[DocDate]
FROM OPDN T0  INNER JOIN PDN1 T1 ON T0.DocEntry = T1.DocEntry
GROUP BY T0.[DocNum], T0.[DocDate] WITH ROLLUP

Any help is appreciated, thank you.

_{Wednesday, July 30, 2008 8:44 PM ✅Answered}

Try:

SELECT

case

when grouping(T0.[DocNum]) = 1 then 'TOTAL'

else cast(T0.[DocNum] as varchar(20)) end as DocNumb, 

          case

when grouping(T0.[DocDate]) = 1 then 'TOTAL'

else cast(T0.[DocDate] as varchar(20)) end as DocDate, 

          SUM(T1.[LineTotal]) as 'Price with Total'
FROM

OPDN T0 

INNER JOIN

PDN1 T1

ON T0.DocEntry = T1.DocEntry

GROUP BY

T0.[DocNum], T0.[DocDate] WITH ROLLUP

order by

grouping(T0.[DocNum]),

grouping(T0.[DocDate])

GO

Examples:

USE northwind

GO

SELECT

CASE

WHEN GROUPING(o.OrderDate) = 0 THEN CONVERT(VARCHAR(20), o.[OrderDate], 101)

ELSE 'Grand Total'

END AS OrderDate,

CASE

WHEN GROUPING(o.[OrderID]) = 0 THEN CONVERT(VARCHAR(20), o.[OrderID])

ELSE 'Total'

END AS OrderID,

SUM(od.[Quantity] * od.[UnitPrice] * (1.00 - od.[Discount])) AS extended_price

FROM

dbo.[Orders] AS o

INNER JOIN

dbo.[Order Details] AS od

ON o.[OrderID] = od.[OrderID]

GROUP BY

o.[OrderDate],

o.[OrderID] WITH ROLLUP

ORDER BY

GROUPING(o.OrderDate),

o.[OrderDate],

GROUPING(o.OrderID),

o.[OrderID]

GO

Grouping By WITH Rollup on multiple columns

http://forums.microsoft.com/MSDN/ShowPost.aspx?PostID=3600901&SiteID=1

AMB

_{Thursday, July 31, 2008 4:46 AM ✅Answered}

Probably not the most performant method, but you can union the results of a second query, with the totals.

I cant test this right now because I do not have SQL next to me, but you should get the idea.

Code Snippet

SELECT CONVERT(VARCHAR(10),T0.[DocNum]),  SUM(T1.[LineTotal]) as 'Price with Total',  T0.[DocDate]
FROM OPDN T0 

INNER JOIN PDN1 T1

ON T0.DocEntry = T1.DocEntry

GROUP BY T0.[DocNum], T0.[DocDate]

union all

SELECT 'TOTAL:', SUM(T1.[LineTotal]),NULL

FROM OPDN T0 

INNER JOIN PDN1 T1

ON T0.DocEntry = T1.DocEntry

_{Wednesday, July 30, 2008 6:17 PM}

ROLLUP ( ) usually  generates the simple GROUP BY aggregate rows, plus subtotal or super-aggregate rows, and also a grand total row.

_{Wednesday, July 30, 2008 6:27 PM}

You can get rid of the NULL values

SELECT case when grouping(T0.[DocNum]) = 1 then 'TOTAL' else T0.[DocNum] end as DocNumb, 

              case when grouping(T0.[DocDate]) = 1 then 'TOTAL' else T0.[DocDate] end as DocDate, 

                                       --might have to cast date

            SUM(T1.[LineTotal]) as 'Price with Total', 
FROM OPDN T0  INNER JOIN PDN1 T1 ON T0.DocEntry = T1.DocEntry
GROUP BY T0.[DocNum], T0.[DocDate] WITH ROLLUP

order by grouping(T0.[DocNum]) , grouping(T0.[DocDate])

Something like this.  Haven't tested it...but know the grouping() function will do the trick somthing like this

_{Wednesday, July 30, 2008 6:27 PM}

Might this work?

Code Snippet

SELECT T0.[DocNum],  SUM(T1.[LineTotal]) as [Price with Total],  T0.[DocDate]
FROM OPDN T0  INNER JOIN PDN1 T1 ON T0.DocEntry = T1.DocEntry

GROUP BY T0.[DocNum], T0.[DocDate]

COMPUTE SUM([Price with Total])

_{Wednesday, July 30, 2008 6:42 PM}

thanks for the quick feedback!

hmm this might work.. but it gave me an error on the date and i dont know how to cast or convert it

the compute code didnt work

_{Wednesday, July 30, 2008 6:45 PM}

If this doesn't fix it, you need to post the error message..  Compute should work, but it is not considered the "best" way to do it anymore.  It returns multiple sets instead of a single set so it is harder to use on the client...

SELECT case when grouping(T0.[DocNum]) = 1 then 'TOTAL' else T0.[DocNum] end as DocNumb, 

              case when grouping(T0.[DocDate]) = 1 then 'TOTAL' else cast(T0.[DocDate] as varchar(20)) end as DocDate, 

                                       --might have to cast date

            SUM(T1.[LineTotal]) as 'Price with Total', 
FROM OPDN T0  INNER JOIN PDN1 T1 ON T0.DocEntry = T1.DocEntry
GROUP BY T0.[DocNum], T0.[DocDate] WITH ROLLUP

order by grouping(T0.[DocNum]) , grouping(T0.[DocDate])

_{Wednesday, July 30, 2008 7:09 PM}

that code gave me this error "Conversion failed when converting the varchar value 'TOTAL' to data type int"