Re: [GENERAL] division by zero issue

[Guy Fraser]

Maybe try something like this :
SELECT
 task_id,
 CASE
  WHEN task_count = '0'
  THEN '0'::int4
  ELSE (task_duration * 
   task_duration_type / 
   task_count) as hours_allocated
 END
FROM
 (SELECT
   task_id,
   task_duration,
   task_duration_type,
   count(user_tasks.task_id) as task_count
 FROM tasks
 LEFT JOIN user_tasks
   ON tasks.task_id = user_tasks.task_id
 WHERE tasks.task_milestone = '0'
 GROUP BY
   tasks.task_id,
   task_duration,
   task_duration_type
 ) as intermediate
;

This was done off the cuff so it may not work as is.
Greg Donald wrote:
Converting some MySQL code to work with Postgres here.
I have this query:
SELECT
 tasks.task_id,
 (tasks.task_duration * tasks.task_duration_type /
count(user_tasks.task_id)) as hours_allocated
FROM tasks
LEFT JOIN user_tasks
 ON tasks.task_id = user_tasks.task_id
WHERE tasks.task_milestone = '0'
GROUP BY
 tasks.task_id,
 task_duration,
 task_duration_type
;
The problem is that sometimes count(user_tasks.task_id) equals zero,
so I get the division by zero error.  Is there a simple way to make
that part of the query fail silently and just equal zero instead of
dividing and producing the error?
TIA..
 


---------------------------(end of broadcast)---------------------------
TIP 1: subscribe and unsubscribe commands go to [EMAIL PROTECTED]