作为Sum的列的总和

Question

我继承了一个查询：

SELECT SUM(Credit) AS Total, Account, Date
FROM
(
SELECT DISTINCT 
CONVERT(char(10), dbo.vCustomer_Invoice.Accounting_Distribution__Document_Date, 101) AS Date
, dbo.vCustomer_Invoice.Accounting_Distribution__Amount_Credit AS "Credit"
, dbo.vCustomer_Invoice.Accounting_Distribution__GL_Account AS "Account" 
FROM dbo.vCustomer_Invoice 
WHERE CONVERT(char(10), dbo.vCustomer_Invoice.Accounting_Distribution__Document_Date, 101)  =  '11/03/2020' AND (dbo.vCustomer_Invoice.Accounting_Distribution__GL_Account  LIKE  '4000%' OR dbo.vCustomer_Invoice.Accounting_Distribution__GL_Account  LIKE  '4100-700-%')
)
AS D
Group By Account, Date;

这给了我一个日期的每个GL_Account的总数。我现在想添加一个列，按日期求和。我过去使用过UNION ALL，但我无法将其用于此查询配置。

如有任何帮助，我们将不胜感激。

Answer 1

您可以为这个新列使用SUM()窗口函数：

SELECT SUM(Credit) AS Total, 
Account, 
Date,
SUM(SUM(Credit)) OVER (PARTITION BY Date) AS Total_By_Date
FROM (
SELECT DISTINCT 
CONVERT(CHAR(10), Accounting_Distribution__Document_Date,101) AS Date,
Accounting_Distribution__Amount_Credit AS Credit, 
Accounting_Distribution__GL_Account AS Account 
FROM dbo.vCustomer_Invoice 
WHERE CONVERT(CHAR(10), Accounting_Distribution__Document_Date, 101) = '11/03/2020' 
AND (Accounting_Distribution__GL_Account LIKE '4000%' OR Accounting_Distribution__GL_Account LIKE '4100-700-%')
) AS D
GROUP BY Account, Date;