division by zero issue

[Greg Donald]

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

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

[Jean-Luc Lachance]

Add :

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

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

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

[Guy Fraser]

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

---------------------------(end of broadcast)---------------------------
TIP 1: subscribe and unsubscribe commands go to ma*******@postgresql.org

[Greg Donald]

On Wed, 15 Sep 2004 12:55:24 -0400, Jean-Luc Lachance
<jl******@sympatico.ca> wrote:

Add :

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

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]

Greg Donald wrote:

I get the error:
aggregates not allowed in WHERE clause

Try HAVING then.

--
Peter Eisentraut
http://developer.postgresql.org/~petere/
---------------------------(end of broadcast)---------------------------
TIP 5: Have you checked our extensive FAQ?

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

[David Fetter]

On Wed, Sep 15, 2004 at 12:23:55PM -0500, Greg Donald wrote:

On Wed, 15 Sep 2004 12:55:24 -0400, Jean-Luc Lachance
<jl******@sympatico.ca> wrote:

Add :

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

I get the error:
aggregates not allowed in WHERE clause

HAVING count(user_tasks.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 da***@fetter.org http://fetter.org/
phone: [phone] mobile: [phone]

Remember to vote!

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

http://archives.postgresql.org

[Tom Lane]

Greg Donald <de******@gmail.com> writes:

On Wed, 15 Sep 2004 12:55:24 -0400, Jean-Luc Lachance
<jl******@sympatico.ca> wrote:

Add :
AND count(user_tasks.task_id) > 0 in the where clause.

I get the error:
aggregates not allowed in WHERE clause

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]

On Wed, 15 Sep 2004 14:01:23 -0400, Tom Lane <tg*@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/

---------------------------(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]

On Wed, Sep 15, 2004 at 01:36:27PM -0500, Greg Donald wrote:

On Wed, 15 Sep 2004 14:01:23 -0400, Tom Lane <tg*@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

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

HTH :)

Cheers,
D
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/

---------------------------(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 da***@fetter.org http://fetter.org/
phone: [phone] mobile: [phone]

Remember to vote!

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

[Alvaro Herrera]

On Wed, Sep 15, 2004 at 01:36:27PM -0500, Greg Donald wrote:

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.

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 ma*******@postgresql.org

[Guy Fraser]

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_type / 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_milestone = '0'
GROUP BY
tasks.task_id,
task_duration,
task_duration_type
) as intermediate;

Greg Donald wrote:

On Wed, 15 Sep 2004 14:01:23 -0400, Tom Lane <tg*@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..

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

On Wed, 15 Sep 2004 12:54:07 -0600, Guy Fraser <gu*@incentre.net> wrote:

This might work better.

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]

On Wed, 15 Sep 2004, 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?

you can avoid it by using the CASE statement:

SELECT
tasks.task_id,
case when count(user_tasks.task_id) > 0 then
(tasks.task_duration * tasks.task_duration_type /
count(user_tasks.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_milestone = '0'
GROUP BY
tasks.task_id,
task_duration,
task_duration_type
;

alternatively you might use HAVING:

SELECT task_id, task_duration * task_duration_type / num_tasks as
hours_allocated
FROM (
SELECT
tasks.task_id,
tasks.task_duration, tasks.task_duration_type,
count(user_tasks.task_id) as num_tasks
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
HAVING count(user_tasks.task_id) > 0
) t
;

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

[Chester Kustarz]

On Wed, 15 Sep 2004, David Fetter wrote:

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

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.

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 YourEmailAddressHere" to ma*******@postgresql.org)

[Martijn van Oosterhout]

On Wed, Sep 15, 2004 at 02:10:45PM -0500, Greg Donald wrote:

On Wed, 15 Sep 2004 12:54:07 -0600, Guy Fraser <gu*@incentre.net> wrote:

This might work better.
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.

It's a called subquery. Everything in the brackets is a query which
produces a result and is aliased to the name "intermediate". 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 <kl*****@svana.org> http://svana.org/kleptog/ 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.

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

iD8DBQFBSK73Y5Twig3Ge+YRAtB1AKCN7Dk4RDTML2IR/yjqou0I7UCO4wCfWwmC
ahRY2R7E7q6KRULLc7iY3Cg=
=V8DK
-----END PGP SIGNATURE-----

[Gaetano Mendola]

Greg Donald wrote:

On Wed, 15 Sep 2004 14:01:23 -0400, Tom Lane <tg*@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.

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

Regards
Gaeatano Mendola

[Gaetano Mendola]

Gaetano Mendola wrote:

Greg Donald wrote:

On Wed, 15 Sep 2004 14:01:23 -0400, Tom Lane <tg*@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.

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