Solved

SQL SUM of nvarchar to decimal

Posted on 2010-09-15
4
2,581 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

Zoho SalesIQ

Hassle-free live chat software re-imagined for business growth. 2 users, always free.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

In this article we will get to know that how can we recover deleted data if it happens accidently. We really can recover deleted rows if we know the time when data is deleted by using the transaction log.
For both online and offline retail, the cross-channel business is the most recent pattern in the B2C trade space.
Familiarize people with the process of retrieving data from SQL Server using an Access pass-thru query. Microsoft Access is a very powerful client/server development tool. One of the ways that you can retrieve data from a SQL Server is by using a pa…
Via a live example, show how to shrink a transaction log file down to a reasonable size.

863 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

23 Experts available now in Live!

Get 1:1 Help Now