0

my query is :

Select sum (node.weight * ans.cn_age) / sum (node.weight) as sum_weight , poll.pollName as poll , poll.endDate as date 
From  report.cm_job_satisfaction ans 
Join poll_management.POLLES poll on ans.cn_pollid = poll.id 
Join poll_management.POSITIONS node on ans.cn_positionid = node.id  
Group by poll , date 
Order by date

but i have error :

ERROR:  division by zero
********** Error **********

ERROR: division by zero
SQL state: 22012

I know because some node.weight is 0 however how to fix this ?

Mr White
  • 97
  • 1
  • 9
  • change `sum (node.weight * ans.cn_age) / sum (node.weight) as sum_weight` to `sum (node.weight * ans.cn_age) / greatest(sum (node.weight),0.00000000000000001) as sum_weight` or apply other logic – Vao Tsun May 03 '17 at 14:41
  • 2
    Simple, don't divide by 0. Not sure what else to say about that... You could add a `having` clause and filter out sums of 0. – Igor May 03 '17 at 14:41
  • What should come out when your divisor `sum (node.weight)` is zero? – amphetamachine May 03 '17 at 14:41
  • 5
    Use `null`: `.../ nullif(sum(node.weight), 0)` –  May 03 '17 at 14:42
  • What result would you prefer? Should it return null or zero or 99999 or what when dividing by 0? – Joe Love May 03 '17 at 14:42
  • @VaoTsun and a_horse_with_no_name it's fixed thank you both of you – Mr White May 03 '17 at 14:52

0 Answers0