Maybe try something like this :
SELECT
task_id,
CASE
WHEN task_count = '0'
THEN '0'::int4
ELSE (task_duration *
task_duration_type /
task_count) as hours_allocated
END
FROM
(SELECT
task_id,
task_duration,
task_duration_type,
count(user_tasks.task_id) as task_count
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
;
This was done off the cuff so it may not work as is.
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?
>
>TIA..
>
>
>
