From: | Guy Fraser <guy(at)incentre(dot)net> |
---|---|
To: | |
Cc: | pgsql-general <pgsql-general(at)postgresql(dot)org> |
Subject: | Re: division by zero issue |
Date: | 2004-09-15 17:01:45 |
Message-ID: | 41487579.7050405@incentre.net |
Views: | Raw Message | Whole Thread | Download mbox | Resend email |
Thread: | |
Lists: | pgsql-general |
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..
>
>
>
From | Date | Subject | |
---|---|---|---|
Next Message | John Sidney-Woollett | 2004-09-15 17:03:47 | Re: psql + autocommit |
Previous Message | Jean-Luc Lachance | 2004-09-15 16:55:24 | Re: division by zero issue |