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 |
|
Grifter
Constraint Violating Yak Guru
274 Posts |
 Posted - 2013-11-29 : 10:30:23
|
HiI am trying to calculate an average within a query:coalesce(field1, 0) + coalesce(field2], 0) + coalesce(field3, 0)/Count [AVG] It seems to be subtracting the value of field3 from the total of 3 fields at some points instead of giving me the average.Some of the results are here: Count Field1 Field2 Field3 Total Average===== ====== ====== ====== ===== =======2 NULL 6 NULL 6 611 NULL 28 13 41 2910 NULL 33 NULL 33 333 NULL 12 NULL 12 1248 NULL 108 1 109 1087 NULL 25 NULL 25 251 NULL 1 NULL 1 13 NULL 13 NULL 13 13141 1 1005 247 1253 10078 NULL 21 11 32 2210 NULL 31 17 48 3213 NULL 81 43 124 8415 1 41 NULL 42 421 NULL 2 NULL 2 224 NULL 105 41 146 1069 NULL 44 2 46 4433 NULL 93 NULL 93 93 thanks for any input on how I may fix G |
|
|
khtan
In (Som, Ni, Yak)
17689 Posts |
Posted - 2013-11-29 : 10:52:31
|
[code](coalesce(field1, 0) + coalesce(field2], 0) + coalesce(field3, 0))/Count [AVG][/code]
KH[spoiler]Time is always against us[/spoiler] |
 |
|
|
Grifter
Constraint Violating Yak Guru
274 Posts |
Posted - 2013-11-29 : 11:44:52
|
Get this: quote:
Msg 102, Level 15, State 1, Line 19Incorrect syntax near ','.Msg 102, Level 15, State 1, Line 27Incorrect syntax near 'c'.
Can't see what's wrong?coalesce((cte.Field1, 0) + coalesce(cte.Field2, 0) + coalesce(cte.Field3, 0))/cte.Count [AVG] Also using cross apply in query:cross apply( select cast(nullif(cte.Field1,0) as decimal(10, 2)) union all select cast(nullif(cte.Field2,0) as decimal(10, 2)) union all select cast(nullif(cte.Field3,0) as decimal(10, 2))) c(sum2) group by DiagnosisDesc,Count, [RAH22-23 Stay], [POST RAH22-23 Stay], [PRE RAH22-23 Stay] |
|
|
|
nigelrivett
Master Smack Fu Yak Hacker
3385 Posts |
Posted - 2013-11-29 : 12:25:01
|
| Bracket in the wrong place(coalesce(cte.Field1, 0) + coalesce(cte.Field2, 0) + coalesce(cte.Field3, 0))/cte.Count [AVG]==========================================Cursors are useful if you don't know sql.SSIS can be used in a similar way.Beer is not cold and it isn't fizzy. |
|
|
|
Grifter
Constraint Violating Yak Guru
274 Posts |
Posted - 2013-12-02 : 09:19:07
|
quote:
Bracket in the wrong place(coalesce(cte.Field1, 0) + coalesce(cte.Field2, 0) + coalesce(cte.Field3, 0))/cte.Count [AVG]
Can't believe I missed that - thanks! |
|
|
|
|
|
|
|
|
Calculation Help