sum the numerator or sum the numerator and divide by the sum of the denominator

Database
Visitor

sum the numerator or sum the numerator and divide by the sum of the denominator

Dear, I have a problem and I am not able to solve it.

 

I have a table and need, depending on the case, to sum the numerator or sum the numerator and divide by the sum of the denominator, based on the result of a case and on the fields that are part of select who influence group by clause.

 

Just to exemplify, here's what I need to do:

  

SELECT
  CASE WHEN 
	COD = 1212 OR
	COD = 1213 OR
	.
	.
	.

THEN 
    sum (numerator) 
ELSE 
    sum (numerator) / sum (denominator)
END 

Returning all values in the same field.

 

Thanks for considering my request.

 

2 REPLIES
Teradata Employee

Re: sum the numerator or sum the numerator and divide by the sum of the denominator

It's not clear what you are asking. On the surface, the CASE expression seems valid.

 

Perhaps the real intent is to calculate SUM(CASE WHEN .... THEN numerator ELSE numerator/denominator END) ?

If denominator can be zero, use NULLIFZERO to avoid divide by zero error.

Teradata Employee

Re: sum the numerator or sum the numerator and divide by the sum of the denominator

Try

SUM( CASE WHEN COD = 1212 OR COD = 1213 OR . . . THEN numerator ELSE 0 END )

+ SUM ( CASE WHEN COD = 1212 OR COD = 1213 OR . . . THEN 0 ELSE numerator END )

               / sum (CASE WHEN COD = 1212 OR COD = 1213 OR . . . THEN 0 ELSE denominator) END )