Re: division by zero issue

Gaetano Mendola wrote:
> 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
> ;

if NULLIF not IFNULL :-)

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

Again, is NULLIF not IFNULL

Regards
Gaetano Mendola