Solved

SQL SUM of nvarchar to decimal

Posted on 2010-09-15
4
2,533 Views
Last Modified: 2012-05-10
Hi everyone,

I have to work with some data that unfortunately is not very friendly in terms of allowing to use SUM. This is what I am trying to do ...

SELECT SUM(CAST(BidValue AS decimal)) AS BidValue FROM Portfolios

BidValue is nvarchar data type. There is nothing I can do to change this, but the issue I am having is I need it to return the decimal places, not round the returned value up or down.

Is there another data type I need to cast to, or is there something I need to add to this?

Thanks in advance.
0
Comment
Question by:DaiWilliams
  • 2
4 Comments
 
LVL 142

Accepted Solution

by:
Guy Hengel [angelIII / a3] earned 500 total points
ID: 33680134
simply:


SELECT SUM(CAST(BidValue AS decimal(20,5) )) AS BidValue FROM Portfolios

Open in new window

0
 
LVL 8

Expert Comment

by:Mohit Vijay
ID: 33680208
SELECT SUM(CAST(BidValue AS decimal(15,7) )) AS BidValue FROM Portfolios
WHERe BidValue IS NOT NULL OR BidValue <> ''

7 is decimal places
0
 
LVL 8

Expert Comment

by:Mohit Vijay
ID: 33680214
A Correction..


SELECT SUM(CAST(BidValue AS decimal(15,7) )) AS BidValue FROM Portfolios
WHERe BidValue IS NOT NULL AND BidValue <> ''

7 is decimal places
0
 
LVL 5

Expert Comment

by:Priya Perumpilavil
ID: 33680296
try this
SELECT  sum( convert (decimal,BidValue)) AS BidValue FROM Portfolios
0

Featured Post

How to improve team productivity

Quip adds documents, spreadsheets, and tasklists to your Slack experience
- Elevate ideas to Quip docs
- Share Quip docs in Slack
- Get notified of changes to your docs
- Available on iOS/Android/Desktop/Web
- Online/Offline

Join & Write a Comment

Slowly Changing Dimension Transformation component in data task flow is very useful for us to manage and control how data changes in SSIS.
Ever needed a SQL 2008 Database replicated/mirrored/log shipped on another server but you can't take the downtime inflicted by initial snapshot or disconnect while T-logs are restored or mirror applied? You can use SQL Server Initialize from Backup…
Via a live example, show how to extract insert data into a SQL Server database table using the Import/Export option and Bulk Insert.
Via a live example, show how to setup several different housekeeping processes for a SQL Server.

746 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question

Need Help in Real-Time?

Connect with top rated Experts

13 Experts available now in Live!

Get 1:1 Help Now