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.

 All Forums
 General SQL Server Forums
 New to SQL Server Programming
 Coalesce

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? Thanks

SELECT COALESCE (SUM(CASE WHEN S_Resolv = 'Yes' THEN 1 ELSE 0 END) * 1.0 / COUNT(S_Resolv), 0) AS Yes
From 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 Yes
From Data

Madhivanan

Failing to plan is Planning to fail
Go to Top of Page

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 0

select coalesce(cast(1/null as varchar(2)),'')
--results in empty string

Em
Go to Top of Page

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
Go to Top of Page

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 Yes
From Data
Go to Top of Page

cutiebo2t
Constraint Violating Yak Guru

256 Posts

Posted - 2008-07-16 : 04:40:50
That would work. Thanks :)
Go to Top of Page

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?
Go to Top of Page

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 Yes
FROM Data[/code]


E 12°55'05.25"
N 56°04'39.16"
Go to Top of Page

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 No

It shows blanks where it should have figures.
Go to Top of Page

cutiebo2t
Constraint Violating Yak Guru

256 Posts

Posted - 2008-07-16 : 22:12:51
any help?
Go to Top of Page

cutiebo2t
Constraint Violating Yak Guru

256 Posts

Posted - 2008-07-17 : 02:23:37
any help with my issue? thanks
Go to Top of Page

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?
Go to Top of Page

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
Go to Top of Page

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 OPENING

Do you know that's the issue? thanks
Go to Top of Page

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"
Go to Top of Page

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.
Go to Top of Page

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"
Go to Top of Page

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 :)
Go to Top of Page

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?
Go to Top of Page

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

Madhivanan

Failing to plan is Planning to fail
Go to Top of Page

cutiebo2t
Constraint Violating Yak Guru

256 Posts

Posted - 2008-07-17 : 06:40:24
So it's not possible?
Go to Top of Page
  Previous Page&nsp;  Next Page

- Advertisement -