Replace NULL in a ROLLUP

Question

Hello, I would like to replace the NULL in the final row when using the ROLLUP, for any other word like 'Total'.

SELECT
SalesOrderID,
SUM(UnitPrice*OrderQty) AS Total
FROM
Sales.SalesOrderDetail
GROUP BY
SalesOrderID WITH ROLLUP

Answer 1

Since SalesOrderID is an integer and the word 'Total' is a varchar value, you must convert the SalesOrderID to varchar and then use ISNULL, for example

SELECT
IsNull(Cast(SalesOrderID As varchar(11)), 'Total'),
SUM(UnitPrice*OrderQty) AS Total
FROM
Sales.SalesOrderDetail
GROUP BY
SalesOrderID WITH ROLLUP

Tom

Answer 2

Hi，@Antonio José Esis Ochoa

Welcome to Microsoft T-SQL Q&A Forum!

Try this：

SELECT  
case when  SalesOrderID is null then 'Total'   
        else CAST(SalesOrderID as varchar(10))   
        end SalesOrderID,  
SUM(UnitPrice*OrderQty) AS Total  
FROM  
Sales.SalesOrderDetail  
GROUP BY SalesOrderID with ROLLUP;  

Best regards,
Bert Zhou

---

If the answer is the right solution, please click "Accept Answer" and kindly upvote it. If you have extra questions about this answer, please click "Comment".
Note: Please follow the steps in our Documentation to enable e-mail notifications if you want to receive the related email notification for this thread.