Re: division by zero issue

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.

I also tried Mr Fraser's suggestion:

SELECT
  tasks.task_id,
  CASE
    WHEN task_count = '0'
    THEN '0'::int4
    ELSE (task_duration * task_duration_type / task_count) as hours_allocated
  END
FROM
(
  SELECT
  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
) as intermediate;

but it's producing an error near the AS for some reason I can't tell.
I tried wrapping it with some parentheses but it didn't help.

TIA..

--
Greg Donald
http://gdconsultants.com/
http://destiney.com/