Author |
Topic |
cutiebo2t
Constraint Violating Yak Guru
256 Posts |
Posted - 2008-07-16 : 04:26:30
|
This statement gives me results of '0' (zero). Is it possible to make it blank instead? ThanksSELECT COALESCE (SUM(CASE WHEN S_Resolv = 'Yes' THEN 1 ELSE 0 END) * 1.0 / COUNT(S_Resolv), 0) AS YesFrom Data |
|
madhivanan
Premature Yak Congratulator
22864 Posts |
Posted - 2008-07-16 : 04:34:20
|
SELECT SUM(CASE WHEN S_Resolv = 'Yes' THEN 1 ELSE 0 END) * 1.0 / COUNT(S_Resolv) AS YesFrom DataMadhivananFailing to plan is Planning to fail |
|
|
elancaster
A very urgent SQL Yakette
1208 Posts |
Posted - 2008-07-16 : 04:35:33
|
i think you'd have to cast it as a varchar intially (which doesn't seem desirable?) as i believe coalesce takes the datatype implicitly from the 1st condition, and blank (i.e. '') is not an numeric value.see as an example...select coalesce(1/null,'')--results in 0select coalesce(cast(1/null as varchar(2)),'')--results in empty stringEm |
|
|
cutiebo2t
Constraint Violating Yak Guru
256 Posts |
Posted - 2008-07-16 : 04:35:51
|
It gives me Null. I prefer blank and not Null. Thanks |
|
|
visakh16
Very Important crosS Applying yaK Herder
52326 Posts |
Posted - 2008-07-16 : 04:39:14
|
quote: Originally posted by cutiebo2t It gives me Null. I prefer blank and not Null. Thanks
SELECT COALESCE (CAST(SUM(CASE WHEN S_Resolv = 'Yes' THEN 1 ELSE 0 END) * 1.0 / COUNT(S_Resolv) AS varchar(10)), '') AS YesFrom Data |
|
|
cutiebo2t
Constraint Violating Yak Guru
256 Posts |
Posted - 2008-07-16 : 04:40:50
|
That would work. Thanks :) |
|
|
cutiebo2t
Constraint Violating Yak Guru
256 Posts |
Posted - 2008-07-16 : 19:21:10
|
I tried it again and this time is not working. I'm getting the error Arithmetic overflow error converting numeric to data type varchar. Any help? |
|
|
SwePeso
Patron Saint of Lost Yaks
30421 Posts |
Posted - 2008-07-16 : 20:21:58
|
[code]SELECT COALESCE(NULLIF(STR(SUM(CASE WHEN S_Resolv = 'Yes' THEN 1.0 ELSE 0.0 END) / COUNT(S_Resolv), 10, 2), 0.0), '') AS YesFROM Data[/code] E 12°55'05.25"N 56°04'39.16" |
|
|
cutiebo2t
Constraint Violating Yak Guru
256 Posts |
Posted - 2008-07-16 : 21:26:06
|
When I tried this:COALESCE (NULLIF (STR(SUM(CASE WHEN S_Resolv = 'No' THEN 1.0 ELSE 0.0 END) / COUNT(S_Resolv), 10, 2), 0.0), '') AS NoIt shows blanks where it should have figures. |
|
|
cutiebo2t
Constraint Violating Yak Guru
256 Posts |
Posted - 2008-07-16 : 22:12:51
|
any help? |
|
|
cutiebo2t
Constraint Violating Yak Guru
256 Posts |
Posted - 2008-07-17 : 02:23:37
|
any help with my issue? thanks |
|
|
visakh16
Very Important crosS Applying yaK Herder
52326 Posts |
Posted - 2008-07-17 : 02:38:01
|
quote: Originally posted by cutiebo2t any help with my issue? thanks
can you give examples of data where you're getting overflow error? |
|
|
cutiebo2t
Constraint Violating Yak Guru
256 Posts |
Posted - 2008-07-17 : 02:39:18
|
when I manually compute it, it has results. But not with this:COALESCE (NULLIF (STR(SUM(CASE WHEN S_Resolv = 'No' THEN 1.0 ELSE 0.0 END) / COUNT(S_Resolv), 10, 2), 0.0), '') AS No |
|
|
cutiebo2t
Constraint Violating Yak Guru
256 Posts |
Posted - 2008-07-17 : 03:25:14
|
I'm getting error on the ','.COALESCE (NULLIF (STR(SUM(CASE WHEN CO_smp = 'Excellent' THEN 1 ELSE 0 END) * 1.0 * 5 + SUM(CASE WHEN CO_smp = 'Good' THEN 1 ELSE 0 END) * 1.0 * 4 + SUM(CASE WHEN CO_smp = 'Neutral' THEN 1 ELSE 0 END) * 1.0 * 3 + SUM(CASE WHEN CO_smp = 'Fair' THEN 1 ELSE 0 END) * 1.0 * 2 + SUM(CASE WHEN CO_smp = 'Poor' THEN 1 ELSE 0 END) * 1.0) / COUNT(CO_smp), 10, 2), 0.0), ' ') AS OPENINGDo you know that's the issue? thanks |
|
|
SwePeso
Patron Saint of Lost Yaks
30421 Posts |
Posted - 2008-07-17 : 03:33:22
|
[code]COALESCE( NULLIF( STR( SUM( CASE WHEN CO_smp = 'Excellent' THEN 5.0 ELSE 0.0 END + CASE WHEN CO_smp = 'Good' THEN 4.0 ELSE 0.0 END + CASE WHEN CO_smp = 'Neutral' THEN 3.0 ELSE 0.0 END + CASE WHEN CO_smp = 'Fair' THEN 2.0 ELSE 0.0 END + CASE WHEN CO_smp = 'Poor' THEN 1.0 ELSE 0.0 END ) / COUNT(CO_smp) , 10, 2) , ' 0.00'), '') AS OPENING[/code] E 12°55'05.25"N 56°04'39.16" |
|
|
cutiebo2t
Constraint Violating Yak Guru
256 Posts |
Posted - 2008-07-17 : 04:18:31
|
Thanks I appreciate it. However, I'm getting this error:Divide by zero error encountered. |
|
|
SwePeso
Patron Saint of Lost Yaks
30421 Posts |
Posted - 2008-07-17 : 04:28:20
|
[code]COALESCE( NULLIF( STR( SUM( CASE WHEN CO_smp = 'Excellent' THEN 5.0 ELSE 0.0 END + CASE WHEN CO_smp = 'Good' THEN 4.0 ELSE 0.0 END + CASE WHEN CO_smp = 'Neutral' THEN 3.0 ELSE 0.0 END + CASE WHEN CO_smp = 'Fair' THEN 2.0 ELSE 0.0 END + CASE WHEN CO_smp = 'Poor' THEN 1.0 ELSE 0.0 END ) / NULLIF(COUNT(CO_smp), 0) , 10, 2) , ' 0.00'), '') AS OPENING[/code] E 12°55'05.25"N 56°04'39.16" |
|
|
cutiebo2t
Constraint Violating Yak Guru
256 Posts |
Posted - 2008-07-17 : 04:37:25
|
I think that would work. I really appreciate your help. Thanks Peso :) |
|
|
cutiebo2t
Constraint Violating Yak Guru
256 Posts |
Posted - 2008-07-17 : 06:35:16
|
I have another question though. Since the results were converted to text format, it won't be possible to make computations like additions from the results? |
|
|
madhivanan
Premature Yak Congratulator
22864 Posts |
Posted - 2008-07-17 : 06:39:01
|
quote: Originally posted by cutiebo2t I have another question though. Since the results were converted to text format, it won't be possible to make computations like additions from the results?
Thats why I always suggest to format the result at front end MadhivananFailing to plan is Planning to fail |
|
|
cutiebo2t
Constraint Violating Yak Guru
256 Posts |
Posted - 2008-07-17 : 06:40:24
|
So it's not possible? |
|
|
Previous Page&nsp;
Next Page
|