SQL Grand Total row from Multi table Select Query

I have been able to use the below SQL query to give me 4 rows of data

SELECT 'Sales Order ' as Type, Format(Sum(T1.C_NetAmountLessDiscount), 
'#.00') As NetAmount, Format(Sum(T1.C_MarginAmount), '#.00') As 
MarginAmount, Format(Sum(T1.C_MarginAmount)/Sum(T1.C_NetAmountLessDiscount), 
'#.00%') As Margin
From T_SalesOrder as T1
Where cast(T1.C_Date as Date) = cast(getdate() as Date) AND T1.C_OrderType 
!= 'BK' AND T1.C_OrderType != 'INV'

Union

SELECT 'Despatch Notes ' AS Type, Format(Sum(T1.C_NetAmountLessDiscount), 
'#.00') As NetAmount, Format(Sum(T1.C_MarginAmount), '#.00') As 
MarginAmount, Format(Sum(T1.C_MarginAmount)/Sum(T1.C_NetAmountLessDiscount), 
'#.00%') As Margin
From T_SalesDeliveryNote as T1
Where cast(T1.C_Date as Date) = cast(getdate() as Date) AND T1.C_Order is 
null AND T1.C_BillingStatus = '0'

Union

SELECT 'Invoices ' AS Type, Format(Sum(T1.C_NetAmountLessDiscount), '#.00') 
As NetAmount, Format(Sum(T1.C_MarginAmount), '#.00') As MarginAmount, 
Format(Sum(T1.C_MarginAmount)/Sum(T1.C_NetAmountLessDiscount), '#.00%') As 
Margin
From T_SalesInvoice as T1
Where cast(T1.C_Date as Date) = cast(getdate() as Date) And 
T1.C_DeliveryNote is null And T1.C_SourceOrder is null and T1.C_InvoiceType 
= '0'

Union

SELECT 'Credit Notes ' AS Type, Format(Sum(T1.C_NetAmountLessDiscount), '- 
#.00') As NetAmount, Format(Sum(T1.C_MarginAmount), '-#.00') As 
MarginAmount, Format(Sum(T1.C_MarginAmount)/Sum(T1.C_NetAmountLessDiscount), 
'#.00%') As Margin
From T_SalesCreditNote as T1
Where cast(T1.C_Date as Date) = cast(getdate() as Date)

This gives me a breakdown of the orders as I need but I also want to have a Grand Total row that sums each column.

If I insert the above sql query into the below

Select 'Grand Total', Sum(CAST(NetAmount AS float)), Sum(CAST(MarginAmount 
AS float)),null
From
(
-----Above SQL Query in Here
)tbl

I get one single line with the correct totals but no breakdown rows.

How can I do this so it displays the four rows of each type and a grand total row at the bottom.

Answer

Use common table expressions (CTE).

WITH t AS(
    -- Your query goes here
    -- SELECT 1 AS A, 'Sales Order' ...
    -- UNION SELECT 2 AS A, 'Despatch Notes' ...
    -- UNION SELECT 3 AS A, 'Invoices' ...
    -- UNION SELECT 4 AS A, 'Credit Notes' ...
)
SELECT
    A,
    Type,
    NetAmount,
    MarginAmount,
    Margin
FROM
    t
UNION SELECT
    5 AS A,
    'Grand Total' AS Type,
    FORMAT(SUM(CAST(NetAmount AS FLOAT)), '#.00') As NetAmount,
    FORMAT(SUM(CAST(MarginAmount AS FLOAT)), '#.00') As MarginAmount,
    NULL
FROM
    t
ORDER BY
    A

Note: I added formatting the total values to ensure the total values of NetAmount and MarginAmount to share the same data type as the breakdown values. Consider to remove formatting the values from your query and add it to the presentation layer of your application.