Re: division by zero issue

Greg Donald wrote:
> On Wed, 15 Sep 2004 14:01:23 -0400, Tom Lane <tgl(at)sss(dot)pgh(dot)pa(dot)us> wrote:
>
>>You need to put it in HAVING, instead.
>>
>>Note also this 7.4.4 bug fix:
>>
>>* Check HAVING restriction before evaluating result list of an aggregate plan
>>
>>which means that this isn't really gonna work unless you are on 7.4.5.
>>(It's fairly astonishing that no one noticed we were doing this in the
>>wrong order until recently, but no one did ...)
>
>
> Thanks, you guys are so helpful.
>
> This works great on my workstation with 7.4.5. But what's the 7.2 way
> of doing it? Our production server is a bit older.

Giving the fact that division by 0 is more near a NULL then a 0, then
you can rewrite you query in this way:

SELECT
tasks.task_id,
(tasks.task_duration * tasks.task_duration_type /
IFNULL(count(user_tasks.task_id),0) ) 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
;

NOTE the IFNULL, and if you are still stuck on having 0 for a division by 0,
then:

SELECT
tasks.task_id,
COALESCE((tasks.task_duration * tasks.task_duration_type /
IFNULL(count(user_tasks.task_id),0) ),0) 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
;

note the COALESCE.

Regards
Gaeatano Mendola