Please start any new threads on our new
site at https://forums.sqlteam.com. We've got lots of great SQL Server
experts to answer whatever question you can come up with.
| Author |
Topic |
|
fuzzyip
Starting Member
35 Posts |
 Posted - 2008-07-24 : 16:39:55
|
| I have a couple columns that I'm trying to add up (price and msrp), they are in varchar(15) format sum(CASE WHEN NULLIF(price,'') IS NULL THEN convert(money, MSRP)ELSE convert(money, price) END) as Revenue,avg(CASE WHEN NULLIF(price,'') IS NULL THEN convert(money, MSRP)ELSE convert(money, price) END) as Avg_PriceI'm using this query right now in the select, but the numbers that come out are different from the numbers that come out when i put it in an excel spreadsheet, I was wondering which application is correct, sql or excel.in the price column, some numbers are just the numbers, but some numbers have a $ in front of them, its random for some reason.eg.6542212569$958621236123655$23654$52263In the msrp column they all have $ in front.I was wondering whether or not this makes a difference in the sum and avg of my select. if so, how do you modify it to make sure that it takes every single number into account? |
|
|
blindman
Master Smack Fu Yak Hacker
2365 Posts |
Posted - 2008-07-24 : 17:02:42
|
| Why the HELL are these numbers being stored as strings?e4 d5 xd5 Nf6 |
 |
|
|
visakh16
Very Important crosS Applying yaK Herder
52326 Posts |
Posted - 2008-07-25 : 00:29:02
|
quote:
Originally posted by fuzzyip
I have a couple columns that I'm trying to add up (price and msrp), they are in varchar(15) format sum(CASE WHEN NULLIF(price,'') IS NULL THEN convert(money, MSRP)ELSE convert(money, price) END) as Revenue,avg(CASE WHEN NULLIF(price,'') IS NULL THEN convert(money, MSRP)ELSE convert(money, price) END) as Avg_PriceI'm using this query right now in the select, but the numbers that come out are different from the numbers that come out when i put it in an excel spreadsheet, I was wondering which application is correct, sql or excel.in the price column, some numbers are just the numbers, but some numbers have a $ in front of them, its random for some reason.eg.6542212569$958621236123655$23654$52263In the msrp column they all have $ in front.I was wondering whether or not this makes a difference in the sum and avg of my select. if so, how do you modify it to make sure that it takes every single number into account?
No need to store this '$' inside the db. just store them as money and the currency formatting can be done at your front end. Always try to use proper datatypes. else you are really making your job difficult. |
|
|
|
|
|
|
|
|
does it matter if a number has a $ in front of it?