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 |
|
crugerenator
Posting Yak Master
126 Posts |
 Posted - 2007-11-02 : 12:13:48
|
| Would this take care of null values in either a.asset or b.asset?SELECT convert(decimal(15,1),(sum(isnull(a.asset,0))/1000.0)+(sum(isnull(b.asset,0))/1000.0)) as total_assetsWhat's throwing me off is that there are multiple a.asset or b.asset for each unique ID. It seems to work, but I'm not following the logic too well. If I were doing this in another language, I would loop through, summing a.asset and b.asset wherever it's not null for each unique ID. |
|
|
Kristen
Test
22859 Posts |
Posted - 2007-11-02 : 12:42:27
|
| Looks fine |
 |
|
|
madhivanan
Premature Yak Congratulator
22864 Posts |
Posted - 2007-11-03 : 01:55:11
|
| The code is correct. Note that null will be omitted when you use SUMSelect sum(col) as col from(select 345 as col union allselect NULL union allselect 100) as tMadhivananFailing to plan is Planning to fail |
|
|
|
Kristen
Test
22859 Posts |
Posted - 2007-11-03 : 02:32:48
|
| Although you would get a warning, which you may have to change your application to ignore |
|
|
|
madhivanan
Premature Yak Congratulator
22864 Posts |
Posted - 2007-11-03 : 02:37:23
|
quote:
Originally posted by Kristen
Although you would get a warning, which you may have to change your application to ignore
I think warnings are not sent to client applicationsMadhivananFailing to plan is Planning to fail |
|
|
|
Kristen
Test
22859 Posts |
Posted - 2007-11-03 : 02:46:11
|
"I think warnings are not sent to client applications"Afraid they are ... actually its probably a good thing because the App. can Catch them. But if you just want to "display the results" its easier to suppress then from SQL end - i.e. in this case by using COALESCE to prevent NULL values from raising a warning.You can turn the warnings off (ANSI_WARNINGS setting IIRC), but we found that caused other headaches from the App.  Kristen |
|
|
|
madhivanan
Premature Yak Congratulator
22864 Posts |
Posted - 2007-11-03 : 03:01:42
|
quote:
Originally posted by Kristen
"I think warnings are not sent to client applications"Afraid they are ... actually its probably a good thing because the App. can Catch them. But if you just want to "display the results" its easier to suppress then from SQL end - i.e. in this case by using COALESCE to prevent NULL values from raising a warning.You can turn the warnings off (ANSI_WARNINGS setting IIRC), but we found that caused other headaches from the App. Kristen
Well. I think I didnt test receiving warnings in front end (as they are not part of error collection)  MadhivananFailing to plan is Planning to fail |
|
|
|
arorarahul.0688
Posting Yak Master
125 Posts |
Posted - 2007-11-03 : 05:18:26
|
| try this it will reduce the complexityselect convert (decimal(15,2), sum(coalesce(a.asset,0)/1000)+sum(coalesce(b.asset,0)/1000))Rahul Arora MCA 07 BatchNCCE Israna, PanipatHRY, INDIA######################IMPOSSIBLE = I+M+POSSIBLE |
|
|
|
Kristen
Test
22859 Posts |
Posted - 2007-11-04 : 02:10:33
|
| "try this it will reduce the complexity"So lets see ...You've taken out a few parenthesis that are redundant. And switch to Integer arithmetic, which will force an implicit conversion. You are also using COALESCE instead of ISNULL. COALESCE is standards-compliant, which I doubt matters to this person, but otherwise they are identical in this usage.Pointless.Kristen |
|
|
|
|
|
|
|
|
SUM when there are null values.