Re: division by zero issue

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..
>
>
>