对于整个选择，我只想将一列的美元总和相加 [英] I just want to SUM the dollar total of the one column, for the entire selection

问题描述

SELECT 	P.GLBankNumber AS [GLBk],
				              P.LegalEntity AS [Entity],
                              P.PortfolioReference AS [Portfolio],
                              P.GLBalance AS [Balance],
                              P.DayKey,
                              C.TransactionNumber AS [Tran#],
                              C.TotalAmount AS [Amount],
                              C.SecurityId AS [SecID],
                              SUM (C.TotalAmount) AS TotalAmt
                              
FROM   [USTG_DM].dbo.tblReport_GLRecon P 
LEFT JOIN [USTG_DM].dbo.tblReport_CashTransactions C on C.DayKey = P.DayKey

--COMPUTE SUM (C.TotalAmount) AS TotalAmt BY P.GLLineNumber
            																	
WHERE		P.DayKey > 20150600 and 
			P.DayKey < 20150700 and
			C.DayKey > 20150600 and 
			C.DayKey < 20150700 and
			P.GLLineNumber = 103600 
			
Aggregate   P.GLBankNumber,
		 P.LegalEntity, 
		 P.PortfolioReference, 
		 P.GLBalance, 
		 P.DayKey,
		 C.TransactionNumber,
		 C.SecurityId,
		 C.TotalAmount
						
--GROUP BY    P.GLBankNumber,
		 --P.LegalEntity, 
		 --P.PortfolioReference, 
		 --P.GLBalance, 
		 --P.DayKey,
		 --C.TransactionNumber,
		 --C.SecurityId,
		 --C.TotalAmount

Msg 102, Level 15, State 1, Line 22
Incorrect syntax near 'Aggregate'.

推荐答案

不确定我是否理解你的问题正确但评论中的部分几乎已经完成。



类似下面的内容你是什么？

Not sure if I understand your question correctly but the parts in comments are almost finished.

Is something like the following what you're after?

SELECT 	P.GLBankNumber AS [GLBk],
	              P.LegalEntity AS [Entity],
                      P.PortfolioReference AS [Portfolio],
                      P.GLBalance AS [Balance],
                      P.DayKey,
                      C.TransactionNumber AS [Tran#],
--                      C.TotalAmount AS [Amount], This cannot be included since you want the sum, not individual values
                      C.SecurityId AS [SecID],
                      SUM (C.TotalAmount) AS TotalAmt
FROM   [USTG_DM].dbo.tblReport_GLRecon P 
LEFT JOIN [USTG_DM].dbo.tblReport_CashTransactions C on C.DayKey = P.DayKey
WHERE		P.DayKey > 20150600 and 
			P.DayKey < 20150700 and
			C.DayKey > 20150600 and 
			C.DayKey < 20150700 and
			P.GLLineNumber = 103600 
GROUP BY    P.GLBankNumber,
	 P.LegalEntity, 
	 P.PortfolioReference, 
	 P.GLBalance, 
	 P.DayKey,
	 C.TransactionNumber,
	 C.SecurityId

增加：



如果您只想要没有明细行的总数，请考虑以下因素：

Addition:

If you want just the total without detail rows, consider the following:

SELECT 	SUM (C.TotalAmount) AS TotalAmt
FROM   [USTG_DM].dbo.tblReport_GLRecon P 
LEFT JOIN [USTG_DM].dbo.tblReport_CashTransactions C on C.DayKey = P.DayKey
WHERE		P.DayKey > 20150600 and 
			P.DayKey < 20150700 and
			C.DayKey > 20150600 and 
			C.DayKey < 20150700 and
			P.GLLineNumber = 103600 

如果你想要一个运行总计，比如

If you want a running total instead, something like

SELECT 	P.GLBankNumber AS [GLBk],
	              P.LegalEntity AS [Entity],
                      P.PortfolioReference AS [Portfolio],
                      P.GLBalance AS [Balance],
                      P.DayKey,
                      C.TransactionNumber AS [Tran#],
                      C.SecurityId AS [SecID],
                      SUM (C.TotalAmount) OVER (ORDER BY C.TransactionNumber) AS TotalAmt
FROM   [USTG_DM].dbo.tblReport_GLRecon P 
LEFT JOIN [USTG_DM].dbo.tblReport_CashTransactions C on C.DayKey = P.DayKey
WHERE		P.DayKey > 20150600 and 
			P.DayKey < 20150700 and
			C.DayKey > 20150600 and 
			C.DayKey < 20150700 and
			P.GLLineNumber = 103600;

但是如果你想要一个单独的项目加上一个额外的行总数，我会建议使用一个不同于SQL的工具来做到这一点。主要是因为SQL不是为了进行格式化而设计的，所以当它可以完成时，会导致一个繁琐的声明...



另外2：



如果要添加额外的行，请使用UNION并在另一个查询中获取总计。我添加了一个额外的列来识别（和排序）行类型。所以也许这样的事情

But if you want a both individual items plus an extra row for the group total I would suggest using a different tool than SQL to do that. Mainly because SQL is not designed to do formatting so while it could be done, it would lead to a cumbersome statement...

Addition 2:

If you want to add an extra row then use UNION and fetch the total in another query. I've added an extra column to identofy (and sort) the row types. So perhaps something like this

SELECT 	'Item' AS RowType,
        P.GLBankNumber AS [GLBk],
	P.LegalEntity AS [Entity],
        P.PortfolioReference AS [Portfolio],
        P.GLBalance AS [Balance],
        P.DayKey,
        C.TransactionNumber AS [Tran#],
        C.SecurityId AS [SecID],
        SUM (C.TotalAmount) AS TotalAmt
FROM   [USTG_DM].dbo.tblReport_GLRecon P 
LEFT JOIN [USTG_DM].dbo.tblReport_CashTransactions C on C.DayKey = P.DayKey
WHERE	P.DayKey > 20150600 and 
	P.DayKey < 20150700 and
	C.DayKey > 20150600 and 
	C.DayKey < 20150700 and
	P.GLLineNumber = 103600 
GROUP BY    P.GLBankNumber,
	 P.LegalEntity, 
	 P.PortfolioReference, 
	 P.GLBalance, 
	 P.DayKey,
	 C.TransactionNumber,
	 C.SecurityId
UNION ALL
SELECT 	'Total' AS RowType,
        NULL AS [GLBk],
	NULL AS [Entity],
        NULL AS [Portfolio],
        NULL AS [Balance],
        NULL,
        NULL AS [Tran#],
        NULL AS [SecID],
        SUM (C.TotalAmount) AS TotalAmt
FROM   [USTG_DM].dbo.tblReport_GLRecon P 
LEFT JOIN [USTG_DM].dbo.tblReport_CashTransactions C on C.DayKey = P.DayKey
WHERE	P.DayKey > 20150600 and 
	P.DayKey < 20150700 and
	C.DayKey > 20150600 and 
	C.DayKey < 20150700 and
	P.GLLineNumber = 103600 
ORDER BY 1,2,3

这篇关于对于整个选择，我只想将一列的美元总和相加的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！