如何在SQL查询中求和两个字段

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

谢谢

7 个解决方案

#1

SUM is 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

#2

Try the following:

试试以下:

SELECT *, (FieldA + FieldB) AS Sum
FROM Table

#3

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]

#4

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并使用+-运算符将它们相加

Select (convert(int, col1)+convert(int, col2)) as summed from tbl1

Hope that helps.

希望有帮助。

#5

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

#6

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编码器的答案。这是最好的方法，这样你就不用在我有类似问题的时候使用这个组了。通过将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

#7

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.

代码所做的就是将这两列相加，然后得到和的总和。这是可行的，但可能不是你想要实现的。