SQL Server create grand total row

Question

I have a table that contains order details. How can I create a row at the end that totals all my subtotals?

SELECT 
    o.order_id, o.itemDescription as Description, 
    o.quantity_shipped as [Quantity], 
    o.itemCost as [Price each], 
    (o.quantity_shipped * CAST(o.itemCost as float)) as [Sub Total] 
FROM 
    dbo.order_items o

Answer 1

This will give you total by Order Id

SELECT o.order_id, SUM((o.quantity_shipped * CAST    (o.itemCost as float))) as [TotalByOrderId] 
    FROM   dbo.order_items o
    GROUP BY o.order_id

This will give you grand total

SELECT SUM((o.quantity_shipped * CAST    (o.itemCost as float))) as [GrandTotal] 
FROM   dbo.order_items o

Answer 2

A way (not the most performance-wise) can be the following:

;WITH CTE AS (
    SELECT o.order_id, o.itemDescription as Description, 
        o.quantity_shipped as [Quantity], 
        o.itemCost as [Price each], 
        (o.quantity_shipped * CAST(o.itemCost as float)) as [Sub Total] 
    FROM dbo.order_items o)
SELECT *
FROM CTE
UNION ALL
SELECT NULL, 'Grand Total', NULL, NULL, SUM([Sub Total])
FROM CTE

Answer 3

Why would you do that? Adding a meaningless total row makes processing a lot more complicated later. Unless you do hierarchical sumbtotals for sub-lines.

The normal way this is handled by having totals as part of the invoice table.