[SOLVED] Query using CTE and UNION ALL with Grand Total at bottom

Query using CTE and UNION ALL with Grand Total at bottom

This is the simplified version or representative of the SQL Server query that I am attempting:

WITH T1 AS (
    SELECT DocNum, CardCode, CardName FROM OINV
)
SELECT CardName AS 'Customer Name', DocNum FROM T1
UNION ALL
SELECT 'Grand Total', COUNT(DocNum) FROM T1
ORDER BY "Customer Name"

In the real query, I cannot avoid using CTE as I need to reference the results of one CTE in another CTE in the same query and there are multiple CTEs.

My main requirement is to have a Grand Total row at the end of the query. The Grand Total row would show some summary figures, like Count, Sum, etc. In the real query, the Grand Total row would itself derive its summary figures based on one of the CTE results.

In the above simplified query, how can I achieve Grand Total at the bottom of the query without adding any additional column in the query result.

In my real query, the 1st CTE gets the list of all the documents with their outstanding balances and the ageing days;

The 2nd query adds additional columns by joining few other tables and categorizes the outstanding amount into ageing buckets like 0-30 days, 30-60 days and so on

And I need to add a Grand Total row to the results of the 2nd query, which should provide total outstanding of all the customers and the totals for each of the ageing buckets categorized in CTE2.

Solution

You would normally work out your grand totals etc in your front end.

But if you have to, why can't you use a sub-query when you select from your CTE e.g.

WITH T1 AS (
    SELECT DocNum, CardCode, CardName
    FROM OINV
)
SELECT [Customer Name], DocNum
FROM
    SELECT CardName AS [Customer Name], DocNum, 0 OrderBy
    FROM T1
    UNION ALL
    SELECT 'Grand Total', COUNT(DocNum), 1 OrderBy
    FROM T1
) X
ORDER BY OrderBy ASC, [Customer Name];

Note: Don't use single quotes to denote a column name. Use either double quotes or square brackets.