Re: division by zero issue

Greg Donald wrote:
> On Wed, 15 Sep 2004 14:01:23 -0400, Tom Lane <tgl@sss.pgh.pa.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