主页 / dba / 问题 / 204592
Accepted
Kinyanjui Kamau
Asked: 2018-04-22 02:03:17 +0800 CST

添加额外的总计列，对每行的所有列值求和

  • 772

我有以下查询

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

输出是： 在此处输入图像描述

我想创建一个额外的列，添加第四列，其中包含三行的行总计。我尝试了以下无济于事：

;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

我如何达到第 4 列总数？

sql-server-2014

1 个回答

  1. Best Answer

    我不确定您是否需要您正在尝试的 CTE。似乎您正在完成第一个查询所需的所有工作，并且可以将其用作 CTE。

    这是您可以运行的模型：

    我将您的第一列命名为“类别”，这样您就可以在最后的SELECT陈述中使用一些东西。还包围了要单独添加的值，ISNULL([Value],0)这样任何NULL值都不会造成麻烦。

    希望这个对你有帮助。

    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;
    • 2

相关问题