Query using CTE and UNION ALL with Grand Total at bottom

Question

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.

Short answer... you can't. The columns must match in a UNION. Another option is add a column with the Total Document Count using the window function ... count(*) over() as TtlCnt — John Cappelletti, Oct 29 '22 at 15:54
Is there any alternative to using CTE where I can use the result of one query in the next query. If the alternative works for me, then I can wrap the whole query in an outer SELECT statement and apply ORDER BY in the outer select without having the column in the SELECT list. Currently, I am unable to wrap the CTE queries in an outer SELECT statement. — AllSolutions, Oct 29 '22 at 15:58
Lookup GROUPING SETS - and ROLLUP. In the final/outer query you can then group everything and add whatever grouping requirements are needed, as well as identify the grouping rows using GROUPING_ID. — Jeff, Oct 29 '22 at 16:29
@Jeff, can you give an example how the above simplistic query can be written using GROUPING SET, so that Grand Total row comes at the end. I am also trying to read up on this topic. — AllSolutions, Oct 29 '22 at 17:13
@JohnCappelletti, have a look at the solution give by DaleK. — AllSolutions, Oct 29 '22 at 20:57
@AllSolutions Yeah, he lined up the columns and data types to fit your requirements. Without SAMPLE DATA we would just be guessing at the data types. — John Cappelletti, Oct 29 '22 at 21:07

score 0 · Accepted Answer · answered Oct 29 '22 at 20:09

0

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.

answered Oct 29 '22 at 20:09

Dale K

25,246
15
42
71

1

Just FYI, there is no frontend here. This is a query report designed for SAP Business One. There is no Crystal Report here. The results of the query report would go as automated email to the users. – AllSolutions Oct 29 '22 at 20:56
Someone forgot `GROUPING SETS`? – Charlieface Oct 30 '22 at 14:50

score 0 · Answer 2 · answered Oct 30 '22 at 14:53

You can use GROUPING SETS for this, and it will only require a single scan of the base tables/CTE

WITH T1 AS (
    SELECT DocNum, CardCode, CardName
    FROM OINV
)
SELECT
  CASE WHEN GROUPING(CardName) = 0 THEN CardName ELSE 'GrandTotal' END AS CardName,
  CASE WHEN GROUPING(CardName) = 0 THEN DocNum   ELSE CAST(COUNT(*) AS varchar(30)) END AS DocNum
FROM T1
GROUP BY GROUPING SETS (
    (CardName, DocNum),
    ()
)
ORDER BY
  GROUPING(CardName),
  CardName;

Query using CTE and UNION ALL with Grand Total at bottom

2 Answers2