COALESCE with MIN

Cinnamon girl

Hello all,

I' trying to query a table(detailed) which has two columns of which one is numerator and the other is denominator part of a field.
I get '0' for denominator for some rows and as it returns NULL I get an error "divide by zero".
So, I'm using a CASE statement like
Now I have to involve my [denominator] into an aggregate function in the GROUP BY clause,which I donot want.
I know this way of writing code is a bit DUMB because I have about 60 more columns on which I have to do this.

So, I was wondering if a COLAESCE function can be used here as
I need some guidance in making this code better and workable.
Thanks in advance for your time and suggestions.

-Cinnamongirl.

Feb 13 '12 #1

Rabbit

Coalesce will work if the denominator is null, not if it's 0.

Feb 13 '12 #2

ck9663

Try using a CTE instead.


~~ CK

Feb 13 '12 #3

Cinnamon girl

How do I use CTE in a recursive manner for other columns while aggregating the table grouping on hour or day or week?

Feb 14 '12 #4

ck9663

Here, try this:

Remember, all the function you use ignores NULL, by default.

Also if both numerator and denominator are integer, the quotient will be integer as well.

Happy Coding!!!


~~ CK

Feb 15 '12 #5

Cinnamon girl

It works...Thank you CK

Feb 15 '12 #6