我们可以在MS SQL服务器中进行不同列的总和 [英] Can we do sum of different columns in MS SQL server

问题描述

我想做不同列数据的总和



我尝试过：



选择（column1 + column2 + column3）为'column4'

但在这种情况下我没有得到正确的o / P.

例如： column1 data = 7.8

column2 data = 7.0

column3 data = 8.9

ten o / P来自7.8.7.9.8.9

解决方案

您的数据库看起来有问题：这些值存储为基于字符串的数据（VARCHAR或NVARCHAR）而不是数字字段。结果，字符串被添加为字符串而不是数字。



虽然你可以在每个列上使用CONVERT，但它是一个严重问题的kludge。更改数据库：始终以最合适的形式存储值：数字的INT或FLOAT，日期的DATE或DATETIME，只有基于字符串的数据存在于NVARCHAR或VARCHAR列中。如果你不这样做，这不是它给你的唯一问题...

由于你的操作数是字符串， +（字符串连接）（Transact-SQL） [ ^ ]运算符。

您必须将操作数转换为数字才能使用算术 +（添加）（Transact-SQL） [ ^ ] operator。

参见CAST AND CONVERT（Transact-SQL） [ ^ ]。





BTW ：使用数字数据类型（而不是 varchar s）持有数字对我来说看起来更自然。

请参阅解决方案1和2以获得解释

 选择（CAST（column1  as   float ）+ CAST（column2  as   float ）+ CAST（column3  as   float ）） as  '  column4' 来自 YourTable 

I want to do sum of different columns data

What I have tried:

select (column1+column2+column3) as 'column4'
but in this case i am not getting proper o/P.
for eg:column1 data=7.8
column2 data=7.0
column3 data= 8.9
ten o/P is coming like 7.8.7.9.8.9

解决方案

Looks like you have a problem with your DB: those values are stored as string based data (VARCHAR or NVARCHAR) instead of numeric fields. As a result, the strings are added as strings instead of as numbers.

While you could use CONVERT on each of the columns, it's a kludge round a serious problem. Change your DB: always store values in the most appropriate form: INT or FLOAT for numbers, DATE or DATETIME for dates, and only string based data goes in NVARCHAR or VARCHAR columns. If you don't this isn't the only problem it's going to give you...

Since your operands are strings, the + (String Concatenation) (Transact-SQL)[^] operator is used.
You have to convert the operands to numbers in order to use the arithmetic + (Add) (Transact-SQL)[^] operator.
See CAST and CONVERT (Transact-SQL)[^].


BTW: using numerical data types (instead of varchars) for holding numbers looks more natural to me.

Refer Solution 1 and 2 for explanation

select  (CAST( column1 as float) + CAST( column2 as float)+ CAST( column3 as float)) as 'column4' from YourTable

这篇关于我们可以在MS SQL服务器中进行不同列的总和的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！