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

CASE WHEN [denominator]= '0'
THEN '0'
ELSE MIN([numerator]/[denominator]) END AS'MIN([numerator]/[denominator])'
,CASE WHEN [denominator]= '0'
THEN '0'
ELSE MAX([numerator]/[denominator]) END AS'MAX([numerator]/[denominator])'
,CASE WHEN [denominator]= '0'
THEN '0'
ELSE AVG([numerator]/[denominator]) END AS'AVG([numerator]/[denominator])'

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

MIN(COLASCE([numerator]/[denominator])) AS 'MIN([numerator]/[denominator])'

I need some guidance in making this code better and workable.
Thanks in advance for your time and suggestions.

-Cinnamongirl.

[Rabbit]

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

[ck9663]

Try using a CTE instead.


~~ CK

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

[ck9663]

Here, try this:

with prepared_data
as
(
   select 
      numerator, denominator, 
      fraction = 
         case 
            when denominator = 0 then 0.00                        
            else numerator/denominator
         end
   from YourTable  
)
select
   min(fraction), max(fraction), avg(fraction)
from prepared_data

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

[Cinnamon girl]

It works...Thank you CK