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
;