如何对 SQL 查询中的两个字段求和
声明：本页面是StackOverFlow热门问题的中英对照翻译，遵循CC BY-SA 4.0协议，如果您需要使用它，必须同样遵循CC BY-SA许可，注明原文地址和作者信息，同时你必须将它归于原作者（不是我）：StackOverFlow
原文地址: http://stackoverflow.com/questions/14877797/
Warning: these are provided under cc-by-sa 4.0 license. You are free to use/share it, But you must attribute it to the original authors (not me):
StackOverFlow
How to SUM two fields within an SQL query
提问by Anton Hughes
I need to get the total of two fields which are within the same row and input that number in a field at the end of that same row.
我需要获取同一行内的两个字段的总数，并在同一行末尾的字段中输入该数字。
This is my code.
这是我的代码。
Sum(tbl1.fld1 + tbl1.fld2) AS [Total]
Is this what the SUM function is used for, or can you only use the SUM function for getting the total of a column?
这是 SUM 函数的用途，还是只能使用 SUM 函数来获取列的总数？
Thanks
谢谢
回答by John Woo
SUMis an aggregate function. It will calculate the total for each group. +is used for calculating two or more columns in a row.
SUM是一个聚合函数。它将计算每个组的总数。+用于计算一行中的两列或更多列。
Consider this example,
考虑这个例子，
ID VALUE1 VALUE2
===================
1 1 2
1 2 2
2 3 4
2 4 5
SELECT ID, SUM(VALUE1), SUM(VALUE2)
FROM tableName
GROUP BY ID
will result
将导致
ID, SUM(VALUE1), SUM(VALUE2)
1 3 4
2 7 9
SELECT ID, VALUE1 + VALUE2
FROM TableName
will result
将导致
ID, VALUE1 + VALUE2
1 3
1 4
2 7
2 9
SELECT ID, SUM(VALUE1 + VALUE2)
FROM tableName
GROUP BY ID
will result
将导致
ID, SUM(VALUE1 + VALUE2)
1 7
2 16
回答by Obl Tobl
Try the following:
请尝试以下操作：
SELECT *, (FieldA + FieldB) AS Sum
FROM Table
回答by Daniel Kelley
SUM is used to sum the value in a column for multiple rows. You can just add your columns together:
SUM 用于对多行的列中的值求和。您可以将列添加在一起：
select tblExportVertexCompliance.TotalDaysOnIncivek + tblExportVertexCompliance.IncivekDaysOtherSource AS [Total Days on Incivek]
回答by E Coder
ID VALUE1 VALUE2
===================
1 1 2
1 2 2
2 3 4
2 4 5
select ID, (coalesce(VALUE1 ,0) + coalesce(VALUE2 ,0) as Total from TableName
回答by LuigiEdlCarno
The sum function only gets the total of a column. In order to sum two values from different columns, convert the values to int and add them up using the +-Operator
sum 函数只获取一列的总数。为了对来自不同列的两个值求和，将值转换为 int 并使用 +-Operator 将它们相加
Select (convert(int, col1)+convert(int, col2)) as summed from tbl1
Hope that helps.
希望有帮助。
回答by Dan Bracuk
If you want to add two columns together, all you have to do is add them. Then you will get the sum of those two columns for each row returned by the query.
如果您想将两列相加，您所要做的就是将它们相加。然后，您将获得查询返回的每一行的这两列的总和。
What your code is doing is adding the two columns together and then getting a sum of the sums. That will work, but it might not be what you are attempting to accomplish.
您的代码正在做的是将两列相加，然后得到总和。这会起作用，但它可能不是您想要完成的。
回答by CelticCoder
Due to my reputation points being less than 50 I could not comment on or vote for E Coder's answer above. This is the best way to do it so you don't have to use the group by as I had a similar issue.
By doing SUM((coalesce(VALUE1 ,0)) + (coalesce(VALUE2 ,0)))as Total this will get you the number you want but also rid you of any error for not performing a Group By.
This was my query and gave me a total count and total amount for the each dealer and then gave me a subtotal for Quality and Risky dealer loans.
由于我的声誉点低于 50，我无法评论或投票支持 E Coder 的上述答案。这是最好的方法，因此您不必使用 group by，因为我遇到了类似的问题。
通过SUM((coalesce(VALUE1 ,0)) + (coalesce(VALUE2 ,0)))按照 Total 进行操作，这将为您提供所需的数字，但也可以避免因不执行 Group By 而出现的任何错误。这是我的查询，给了我每个经销商的总数和总金额，然后给了我质量和风险经销商贷款的小计。
SELECT
DISTINCT STEP1.DEALER_NBR
,COUNT(*) AS DLR_TOT_CNT
,SUM((COALESCE(DLR_QLTY,0))+(COALESCE(DLR_RISKY,0))) AS DLR_TOT_AMT
,COUNT(STEP1.DLR_QLTY) AS DLR_QLTY_CNT
,SUM(STEP1.DLR_QLTY) AS DLR_QLTY_AMT
,COUNT(STEP1.DLR_RISKY) AS DLR_RISKY_CNT
,SUM(STEP1.DLR_RISKY) AS DLR_RISKY_AMT
FROM STEP1
WHERE DLR_QLTY IS NOT NULL OR DLR_RISKY IS NOT NULL
GROUP BY STEP1.DEALER_NBR
回答by Barb Dawdy
Just a reminder on adding columns. If one of the values is NULL the total of those columns becomes NULL. Thus why some posters have recommended coalesce with the second parameter being 0
只是关于添加列的提醒。如果其中一个值为 NULL，则这些列的总数将变为 NULL。因此为什么有些发帖人建议将第二个参数设为 0 进行合并
I know this was an older posting but wanted to add this for completeness.
我知道这是一个较旧的帖子，但为了完整起见，我想添加它。
