CODEWITHSUNDEEP
sqlsql-server
Wyseguy
Wyseguy

Reputation: 21

Sum then divide by issue

Currently I'm trying to sum two columns and then divide by another, example;

'''SUM(M.Bonus) + ISNULL(SUM(Q.Bonus),0) / SUM(Salary.M)'''

Q.Bonus contains null values. If I remove ISNULL it gives me the correct answer but won't calculate some because of null values. When I do it with the ISNULL it just gives me the outcome of the sum.

Upvotes: 0

Views: 174

Answers (4)

Gordon Linoff
Gordon Linoff

Reputation: 1269553

You need additional parentheses. But I would recommend using NULLIF() to avoid divide-by-zero errors:

(SUM(M.Bonus) + COALESCE(SUM(Q.Bonus), 0)) / NULLIF(SUM(Salary.M), 0)

Upvotes: 1

Sean Lange
Sean Lange

Reputation: 33571

I think you want something along these lines. You only need one aggregate. Also parenthesis to ensure the calculation is performed correctly.

SUM((M.Bonus + ISNULL(Q.Bonus, 0)) / M.Salary)

Upvotes: 1

JIKEN
JIKEN

Reputation: 337

You need to correct ISNULL() function syntax.
You need to take care devide by zero error as below.

This query gives you answer.

SUM(ISNULL(M.Bonus,0)) + SUM(ISNULL(Q.Bonus,0)) / ISNULL(SUM(ISNULL(M.Salary,0)),1)

Note: I think that you may wrote salary alias wrongly. So, I have corrected.

Upvotes: 1

Mack
Mack

Reputation: 2552

From your very brief question I would guess that you have a basic BODMAS maths problem

SUM(M.Bonus) + ISNULL(SUM(Q.Bonus),0) / SUM(Salary.M)

should probably be

(SUM(M.Bonus) + ISNULL(SUM(Q.Bonus),0)) / SUM(Salary.M)

Note the extra brackets!

Without further context and examples code it's really difficult to see what you are really asking. See this page for further info on asking a good question.

Upvotes: 4

Related Questions