Adding extra total column that sums all column values on each row

Question

I have the below query

SELECT 'Total_Invoices', SUM(T0.[DocTotal]) - (SUM(T0.[VatSum])-SUM(T0.[WTSum])) as "Amnt(Exl)", SUM(T0.[VatSum]) as "VAT", SUM(T0.[DocTotal]) as "Amnt(Incl)" FROM OINV T0 WHERE T0.[Series] <>'80' AND
DateDiff(MM,T0.DocDate,'2017-02-02')=0
UNION ALL
SELECT 'Total_DownPayment', SUM(T0.[DocTotal]) - (SUM(T0.[VatSum])-SUM(T0.[WTSum])) as "Amnt(Exl)", SUM(T0.[VatSum]) as "VAT", SUM(T0.[DocTotal]) as "Amnt(Incl)" FROM ODPI T0 WHERE
DateDiff(MM,T0.DocDate,'2017-02-02')=0
UNION ALL
SELECT 'Total_CreditNotes', (SUM(T0.[VatSum])-SUM(T0.[WTSum]))-SUM(T0.[DocTotal]) as "Amnt(Exl)", -SUM(T0.[VatSum]) as "VAT", -SUM(T0.[DocTotal]) as "Amnt(Incl)" FROM ORIN T0 WHERE T0.[Series]='5' AND 
DateDiff(MM,T0.DocDate,'2017-02-02')=0

Output is: enter image description here

I want to create an extra column that adds a fourth column with row totals for the three rows. I have tried the below to no avail:

;WITH CTE AS 
(
SELECT * FROM
(
 SELECT SUM(T0.[DocTotal]) - (SUM(T0.[VatSum])-SUM(T0.[WTSum])) as "Amnt(Exl)", SUM(T0.[VatSum]) as "VAT", SUM(T0.[DocTotal]) as "Amnt(Incl)" FROM OINV T0 WHERE T0.[Series] <>'80' AND
 DateDiff(MM,T0.DocDate,'2017-02-02')=0 
)AS Qry1
 UNION ALL
 SELECT * FROM
(
 SELECT SUM(T0.[DocTotal]) - (SUM(T0.[VatSum])-SUM(T0.[WTSum])) as "Amnt(Exl)", SUM(T0.[VatSum]) as "VAT", SUM(T0.[DocTotal]) as "Amnt(Incl)" FROM ODPI T0 WHERE
 DateDiff(MM,T0.DocDate,'2017-02-02')=0
)AS Qry2
 UNION ALL
 SELECT * FROM
(
 SELECT (SUM(T0.[VatSum])-SUM(T0.[WTSum]))-SUM(T0.[DocTotal]) as "Amnt(Exl)", -SUM(T0.[VatSum]) as "VAT", -SUM(T0.[DocTotal]) as "Amnt(Incl)" FROM ORIN T0 WHERE T0.[Series]='5' AND 
 DateDiff(MM,T0.DocDate,'2017-02-02')=0
)AS Qry3
)
SELECT *, Qry1 + Qry2 + Qry3 AS TotalSum
FROM CTE

How do I achieve this 4th column total?

• ypercubeᵀᴹ: Do you want row totals or column totals? It doesn't make much sense to add the Amount (Excluded) with the Amount (Included).

Answer 1

I'm not sure you need the CTE you're attempting. Seems like you're doing all the work needed in your first query and can use THAT as the CTE.

Here's a mockup you may be able to just run:

I named your first column "Category" so that you'd have something to use in the final SELECT statement. Also surrounded the values to be added in individual ISNULL([Value],0) so that any NULL values wouldn't cause trouble.

Hope this helps a bit.

WITH CTE_Data
AS
(
SELECT 'Total_Invoices' AS Category, SUM(T0.[DocTotal]) - (SUM(T0.[VatSum])-SUM(T0.[WTSum])) as "Amnt(Exl)", SUM(T0.[VatSum]) as "VAT", SUM(T0.[DocTotal]) as "Amnt(Incl)" FROM OINV T0 WHERE T0.[Series] <>'80' AND
DateDiff(MM,T0.DocDate,'2017-02-02')=0
UNION ALL
SELECT 'Total_DownPayment', SUM(T0.[DocTotal]) - (SUM(T0.[VatSum])-SUM(T0.[WTSum])) as "Amnt(Exl)", SUM(T0.[VatSum]) as "VAT", SUM(T0.[DocTotal]) as "Amnt(Incl)" FROM ODPI T0 WHERE
DateDiff(MM,T0.DocDate,'2017-02-02')=0
UNION ALL
SELECT 'Total_CreditNotes', (SUM(T0.[VatSum])-SUM(T0.[WTSum]))-SUM(T0.[DocTotal]) as "Amnt(Exl)", -SUM(T0.[VatSum]) as "VAT", -SUM(T0.[DocTotal]) as "Amnt(Incl)" FROM ORIN T0 WHERE T0.[Series]='5' AND 
DateDiff(MM,T0.DocDate,'2017-02-02')=0
)
SELECT Category, 
 [Amnt(Exl)],
 [VAT],
 [Amnt(Incl)],
 ISNULL([Amnt(Exl)],0) + ISNULL([VAT],0) + ISNULL([Amnt(Incl)],0) AS SumRequested
FROM CTE_Data;

• Kinyanjui Kamau: This is what I needed to be doing. Great, just a comma after [Amnt(Incl)].
• SQLDevDBA: Edited accordingly. I'd also evaluate your WHERE clauses a bit to ensure that you're not ignoring indexes by running functions on indexed columns.