Dividing 2 column expression does not give a valid result

Question

I am doing the following calculation ,

SUM(A by Category)/SUM(A + B + C by Category)

Both the calculation individually gives values but when division is done the result is always 0. Could anyone help what's wrong with the above expression?

Answer 1

Assuming that A, B and C are integers, theirs sums will also be integeres. In that case, Obiee will perform an integer division. Assuming A, B and C are all positive, the denominator will always be larger than the nominator, which explains the zero result. You can resolve this issue by explicitly casting the sums to doubles:

CAST(SUM(A by Category) AS DOUBLE)/CAST(SUM(A + B + C by Category) AS DOUBLE)