division by zero issue

**Jean-Luc Lachance** · Nov 23 '05, 02:07 AM

Re: division by zero issue

Add :

AND count(user_task s.task_id) > 0 in the where clause.

Greg Donald wrote:
[color=blue]
> Converting some MySQL code to work with Postgres here.
>
> I have this query:
>
> SELECT
> tasks.task_id,
> (tasks.task_dur ation * tasks.task_dura tion_type /
> count(user_task s.task_id)) as hours_allocated
> FROM tasks
> LEFT JOIN user_tasks
> ON tasks.task_id = user_tasks.task _id
> WHERE tasks.task_mile stone = '0'
> GROUP BY
> tasks.task_id,
> task_duration,
> task_duration_t ype
> ;
>
> The problem is that sometimes count(user_task s.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..
>[/color]

---------------------------(end of broadcast)---------------------------
TIP 4: Don't 'kill -9' the postmaster

**Guy Fraser** · Nov 23 '05, 02:07 AM

Re: division by zero issue

Maybe try something like this :

SELECT
task_id,
CASE
WHEN task_count = '0'
THEN '0'::int4
ELSE (task_duration *
task_duration_t ype /
task_count) as hours_allocated
END
FROM
(SELECT
task_id,
task_duration,
task_duration_t ype,
count(user_task s.task_id) as task_count
FROM tasks
LEFT JOIN user_tasks
ON tasks.task_id = user_tasks.task _id
WHERE tasks.task_mile stone = '0'
GROUP BY
tasks.task_id,
task_duration,
task_duration_t ype
) as intermediate
;

This was done off the cuff so it may not work as is.

Greg Donald wrote:
[color=blue]
>Converting some MySQL code to work with Postgres here.
>
>I have this query:
>
>SELECT
> tasks.task_id,
> (tasks.task_dur ation * tasks.task_dura tion_type /
>count(user_tas ks.task_id)) as hours_allocated
>FROM tasks
>LEFT JOIN user_tasks
> ON tasks.task_id = user_tasks.task _id
>WHERE tasks.task_mile stone = '0'
>GROUP BY
> tasks.task_id,
> task_duration,
> task_duration_t ype
>;
>
>The problem is that sometimes count(user_task s.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..
>
>
>[/color]

---------------------------(end of broadcast)---------------------------
TIP 1: subscribe and unsubscribe commands go to majordomo@postg resql.org

**Greg Donald** · Nov 23 '05, 02:07 AM

Re: division by zero issue

On Wed, 15 Sep 2004 12:55:24 -0400, Jean-Luc Lachance
<jllachan@sympa tico.ca> wrote:[color=blue]
> Add :
>
> AND count(user_task s.task_id) > 0 in the where clause.[/color]

I get the error:
aggregates not allowed in WHERE clause

--
Greg Donald

http://gdconsultants.com/

http://destiney.com/

---------------------------(end of broadcast)---------------------------
TIP 7: don't forget to increase your free space map settings

**Peter Eisentraut** · Nov 23 '05, 02:07 AM

Re: division by zero issue

Greg Donald wrote:[color=blue]
> I get the error:
> aggregates not allowed in WHERE clause[/color]

Try HAVING then.

--
Peter Eisentraut

http://developer.postgresql.org/~petere/

---------------------------(end of broadcast)---------------------------
TIP 5: Have you checked our extensive FAQ?

PostgreSQL: Not Found

http://www.postgresql.org/docs/faqs/FAQ.html

**David Fetter** · Nov 23 '05, 02:08 AM

Re: division by zero issue

On Wed, Sep 15, 2004 at 12:23:55PM -0500, Greg Donald wrote:[color=blue]
> On Wed, 15 Sep 2004 12:55:24 -0400, Jean-Luc Lachance
> <jllachan@sympa tico.ca> wrote:[color=green]
> > Add :
> >
> > AND count(user_task s.task_id) > 0 in the where clause.[/color]
>
> I get the error:
> aggregates not allowed in WHERE clause[/color]

HAVING count(user_task s.task_id) > 0

I know it's a little weird to have WHERE for non-aggregate and HAVING
for aggregates, but that's the SQL standard...

Cheers,
D
--
David Fetter david@fetter.or g http://fetter.org/
phone: +1 510 893 6100 mobile: +1 415 235 3778

Remember to vote!

---------------------------(end of broadcast)---------------------------
TIP 6: Have you searched our list archives?

PostgreSQL: PostgreSQL Mailing List Archives

http://archives.postgresql.org

**Tom Lane** · Nov 23 '05, 02:08 AM

Re: division by zero issue

Greg Donald <destiney@gmail .com> writes:[color=blue]
> On Wed, 15 Sep 2004 12:55:24 -0400, Jean-Luc Lachance
> <jllachan@sympa tico.ca> wrote:[color=green]
>> Add :
>> AND count(user_task s.task_id) > 0 in the where clause.[/color][/color]
[color=blue]
> I get the error:
> aggregates not allowed in WHERE clause[/color]

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

regards, tom lane

---------------------------(end of broadcast)---------------------------
TIP 7: don't forget to increase your free space map settings

**Greg Donald** · Nov 23 '05, 02:08 AM

Re: division by zero issue

On Wed, 15 Sep 2004 14:01:23 -0400, Tom Lane <tgl@sss.pgh.pa .us> wrote:[color=blue]
> 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 ...)[/color]

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_t ype / 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_mile stone = '0'
GROUP BY
tasks.task_id,
task_duration,
task_duration_t ype
) 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/

---------------------------(end of broadcast)---------------------------
TIP 9: the planner will ignore your desire to choose an index scan if your
joining column's datatypes do not match

**David Fetter** · Nov 23 '05, 02:08 AM

Re: division by zero issue

On Wed, Sep 15, 2004 at 01:36:27PM -0500, Greg Donald wrote:[color=blue]
> On Wed, 15 Sep 2004 14:01:23 -0400, Tom Lane <tgl@sss.pgh.pa .us> wrote:[color=green]
> > 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 ...)[/color]
>
> 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_t ype / task_count) as hours_allocated
> END[/color]

This AS labeling should come at the end of the CASE..END construct.

HTH :)

Cheers,
D
[color=blue]
> FROM
> (
> SELECT
> FROM tasks
> LEFT JOIN user_tasks
> ON tasks.task_id = user_tasks.task _id
> WHERE tasks.task_mile stone = '0'
> GROUP BY
> tasks.task_id,
> task_duration,
> task_duration_t ype
> ) 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/
>
> ---------------------------(end of broadcast)---------------------------
> TIP 9: the planner will ignore your desire to choose an index scan if your
> joining column's datatypes do not match[/color]

--
David Fetter david@fetter.or g http://fetter.org/
phone: +1 510 893 6100 mobile: +1 415 235 3778

Remember to vote!

---------------------------(end of broadcast)---------------------------
TIP 3: if posting/reading through Usenet, please send an appropriate
subscribe-nomail command to majordomo@postg resql.org so that your
message can get through to the mailing list cleanly

**Alvaro Herrera** · Nov 23 '05, 02:08 AM

Re: division by zero issue

On Wed, Sep 15, 2004 at 01:36:27PM -0500, Greg Donald wrote:
[color=blue]
> I also tried Mr Fraser's suggestion:
>
> SELECT
> tasks.task_id,
> CASE
> WHEN task_count = '0'
> THEN '0'::int4
> ELSE (task_duration * task_duration_t ype / 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_mile stone = '0'
> GROUP BY
> tasks.task_id,
> task_duration,
> task_duration_t ype
> ) 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.[/color]

Try putting the AS outside the CASE/END ...

--
Alvaro Herrera (<alvherre[a]dcc.uchile.cl>)
"Coge la flor que hoy nace alegre, ufana. ¿Quién sabe si nacera otra mañana?"

---------------------------(end of broadcast)---------------------------
TIP 1: subscribe and unsubscribe commands go to majordomo@postg resql.org

**Guy Fraser** · Nov 23 '05, 02:08 AM

Re: division by zero issue

Doh...,

I messed up

This might work better.

SELECT
tasks.task_id,
CASE
WHEN task_count = '0'
THEN '0'::int4
ELSE (task_duration * task_duration_t ype / task_count)
END as hours_allocated
FROM
(
SELECT
FROM tasks
LEFT JOIN user_tasks
ON tasks.task_id = user_tasks.task _id
WHERE tasks.task_mile stone = '0'
GROUP BY
tasks.task_id,
task_duration,
task_duration_t ype
) as intermediate;

Greg Donald wrote:
[color=blue]
>On Wed, 15 Sep 2004 14:01:23 -0400, Tom Lane <tgl@sss.pgh.pa .us> wrote:
>
>[color=green]
>>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 ...)
>>
>>[/color]
>
>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_t ype / 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_mile stone = '0'
> GROUP BY
> tasks.task_id,
> task_duration,
> task_duration_t ype
>) 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..
>
>
>[/color]

--
Guy Fraser
Network Administrator
The Internet Centre
780-450-6787 , 1-888-450-6787

There is a fine line between genius and lunacy, fear not, walk the
line with pride. Not all things will end up as you wanted, but you
will certainly discover things the meek and timid will miss out on.

---------------------------(end of broadcast)---------------------------
TIP 8: explain analyze is your friend

**Greg Donald** · Nov 23 '05, 02:08 AM

Re: division by zero issue

On Wed, 15 Sep 2004 12:54:07 -0600, Guy Fraser <guy@incentre.n et> wrote:[color=blue]
> This might work better.[/color]

Thanks, I got it working finally. It wouldn't go without any fields
in the second SELECT, but I added them and now it works.

Where can I find docs for the 'as intermediate' part of this query. I
never heard of it and can't seem to find it in the manual other than
it's listing in the SQL keywords table. I see what it does but still
want to read the docs about it.

--
Greg Donald

http://gdconsultants.com/

http://destiney.com/

---------------------------(end of broadcast)---------------------------
TIP 7: don't forget to increase your free space map settings

**Chester Kustarz** · Nov 23 '05, 02:08 AM

Re: division by zero issue

On Wed, 15 Sep 2004, Greg Donald wrote:[color=blue]
> Converting some MySQL code to work with Postgres here.
>
> I have this query:
>
> SELECT
> tasks.task_id,
> (tasks.task_dur ation * tasks.task_dura tion_type /
> count(user_task s.task_id)) as hours_allocated
> FROM tasks
> LEFT JOIN user_tasks
> ON tasks.task_id = user_tasks.task _id
> WHERE tasks.task_mile stone = '0'
> GROUP BY
> tasks.task_id,
> task_duration,
> task_duration_t ype
> ;
>
> The problem is that sometimes count(user_task s.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?[/color]

you can avoid it by using the CASE statement:

SELECT
tasks.task_id,
case when count(user_task s.task_id) > 0 then
(tasks.task_dur ation * tasks.task_dura tion_type /
count(user_task s.task_id)) else 0.0 end as hours_allocated
FROM tasks
LEFT JOIN user_tasks
ON tasks.task_id = user_tasks.task _id
WHERE tasks.task_mile stone = '0'
GROUP BY
tasks.task_id,
task_duration,
task_duration_t ype
;

alternatively you might use HAVING:

SELECT task_id, task_duration * task_duration_t ype / num_tasks as
hours_allocated
FROM (
SELECT
tasks.task_id,
tasks.task_dura tion, tasks.task_dura tion_type,
count(user_task s.task_id) as num_tasks
FROM tasks
LEFT JOIN user_tasks
ON tasks.task_id = user_tasks.task _id
WHERE tasks.task_mile stone = '0'
GROUP BY
tasks.task_id,
task_duration,
task_duration_t ype
HAVING count(user_task s.task_id) > 0
) t
;

---------------------------(end of broadcast)---------------------------
TIP 3: if posting/reading through Usenet, please send an appropriate
subscribe-nomail command to majordomo@postg resql.org so that your
message can get through to the mailing list cleanly

**Chester Kustarz** · Nov 23 '05, 02:08 AM

Re: division by zero issue

On Wed, 15 Sep 2004, David Fetter wrote:[color=blue]
> I know it's a little weird to have WHERE for non-aggregate and HAVING
> for aggregates, but that's the SQL standard...[/color]

the WHERE clause strips rows before grouping. the HAVING clause operates
after grouping. so it's not so much aggregate vs. non-aggregate as it is
about order of operations.

Complex Queries

http://philip.greenspun.com/sql/complex-queries.html

"The WHERE clause restricts which rows are returned. The HAVING clause operates analogously but on groups of rows."

---------------------------(end of broadcast)---------------------------
TIP 2: you can get off all lists at once with the unregister command
(send "unregister YourEmailAddres sHere" to majordomo@postg resql.org)

**Martijn van Oosterhout** · Nov 23 '05, 02:08 AM

Re: division by zero issue

On Wed, Sep 15, 2004 at 02:10:45PM -0500, Greg Donald wrote:[color=blue]
> On Wed, 15 Sep 2004 12:54:07 -0600, Guy Fraser <guy@incentre.n et> wrote:[color=green]
> > This might work better.[/color]
>
> Thanks, I got it working finally. It wouldn't go without any fields
> in the second SELECT, but I added them and now it works.
>
> Where can I find docs for the 'as intermediate' part of this query. I
> never heard of it and can't seem to find it in the manual other than
> it's listing in the SQL keywords table. I see what it does but still
> want to read the docs about it.[/color]

It's a called subquery. Everything in the brackets is a query which
produces a result and is aliased to the name "intermedia te". The outer
query can then use it as a source table like any other table.

Ofcourse, optimisation might mean it gets optimised away, but that's
the basic idea...

--
Martijn van Oosterhout <kleptog@svana. org> http://svana.org/kleptog/[color=blue]
> Patent. n. Genius is 5% inspiration and 95% perspiration. A patent is a
> tool for doing 5% of the work and then sitting around waiting for someone
> else to do the other 95% so you can sue them.[/color]

-----BEGIN PGP SIGNATURE-----
Version: GnuPG v1.0.6 (GNU/Linux)
Comment: For info see http://www.gnupg.org

iD8DBQFBSK73Y5T wig3Ge+YRAtB1AK CN7Dk4RDTML2IR/yjqou0I7UCO4wCf WwmC
ahRY2R7E7q6KRUL Lc7iY3Cg=
=V8DK
-----END PGP SIGNATURE-----