如何通过将列转换为金钱来获得列的总和(目前它们在varchar中) [英] How to get Sum of column by converting them to money(Currently they are in varchar)
问题描述
我有一张桌子让我说我已经将NetAmount存储为varchar而且表格中有一个重复的TechId
目前数据的格式是这样的
PK TechId NetAmount
1 2003 500
2 2003 200
3 2004 400
4 2004 100
我需要这样的数据
TechID CountOfTechId SumOfNetAmount
2003 2 700
2004 2 500
我可以使用Count(TechId)和group获得技术ID计数通过
但是如何添加varchar中的净额
-Thanks
I Have a table let's say a in which I have stored NetAmount as varchar and There is a TechId which is repeating in the table
Currently data is in format like this
PK TechId NetAmount
1 2003 500
2 2003 200
3 2004 400
4 2004 100
I need data like this
TechID CountOfTechId SumOfNetAmount
2003 2 700
2004 2 500
I am able to get Count of Tech ID using Count(TechId) and group by
but how can I add Net amount which is in varchar
-Thanks
推荐答案
规则之一:将数据存储在适当的列中。
更改数据库以使用数字列和您的问题(以及您尚未遇到的一堆其他未来问题)将离开。
如果你去on't,然后在将来的某个时候,不知何故,其中一个值不会是数字,你的应用程序将失败 - 你不会知道原因。
立即修复,所有值都经过验证和更改,以后您无法输入无效数字。
Rule one of data: store it in appropriate columns.
Change your database to use a numeric column and your problem (and the bunch of other future problems you haven't met yet) will go away.
If you don't, then at some point in the future, somehow one of those values is not going to be numeric, and your app will fail - and you won't know why.
Fix it now, all values are validated and changed and you can't enter invalid numbers in future.
试试这个
sum(cast( NetAmount as Money))
try this
sum(cast(NetAmount as Money))
这篇关于如何通过将列转换为金钱来获得列的总和(目前它们在varchar中)的文章就介绍到这了,希望我们推荐的答案对大家有所帮助,也希望大家多多支持IT屋!