Click here to Skip to main content
Rate this: bad
good
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
 
-Thanks
Posted 20-Aug-14 5:05am
Rate this: bad
good
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.
  Permalink  
Comments
Vishal Pand3y at 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 at 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
good
Please Sign up or sign in to vote.

Solution 2

try this
 
sum(cast(NetAmount as Money))
  Permalink  

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



Advertise | Privacy | Mobile
Web03 | 2.8.150302.1 | Last Updated 20 Aug 2014
Copyright © CodeProject, 1999-2015
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