Re: division by zero issue

On Wed, 15 Sep 2004, 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?

you can avoid it by using the CASE statement:

 SELECT
   tasks.task_id,
   case when count(user_tasks.task_id) > 0 then
 (tasks.task_duration * tasks.task_duration_type /
 count(user_tasks.task_id)) else 0.0 end 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
 ;

alternatively you might use HAVING:

SELECT task_id, task_duration * task_duration_type / num_tasks as
    hours_allocated
FROM (
   SELECT
   tasks.task_id,
   tasks.task_duration, tasks.task_duration_type,
   count(user_tasks.task_id) as num_tasks
 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
 HAVING count(user_tasks.task_id) > 0
 ) t
 ;