Click here to Skip to main content
13,146,934 members (78,464 online)
Rate this:
Please Sign up or sign in to vote.
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

Posted 20-Aug-14 4:05am
Rate this: bad
Please Sign up or sign in to vote.

Solution 1

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.
Vishal Pand3y 21-Aug-14 8:25am
@OriginalGriff I now it's wrong but we have imported this data from salesforce just couldn't change it then
OriginalGriff 21-Aug-14 8:33am
So change it now: and if you get further data from sales, change that as well.
The problem is that salesmen don't care: if it doesn't affect their commission it's irrelevant. So you don't know it's valid numeric data until the first time you come to use it.
At which point your app crashes and it's your fault, not sales.
It's not difficult to change: just try using SQL to alter the field type. If it works, it will convert the numbers as well...if it doesn't, then you know there is a problem in tehre waiting to bite you!
Rate this: bad
Please Sign up or sign in to vote.

Solution 2

try this

sum(cast(NetAmount as Money))

This content, along with any associated source code and files, is licensed under The Code Project Open License (CPOL)

  Print Answers RSS
Top Experts
Last 24hrsThis month

Advertise | Privacy |
Web03 | 2.8.170915.1 | Last Updated 20 Aug 2014
Copyright © CodeProject, 1999-2017
All Rights Reserved. Terms of Service
Layout: fixed | fluid

CodeProject, 503-250 Ferrand Drive Toronto Ontario, M3C 3G8 Canada +1 416-849-8900 x 100