Postgres Pro
Downloads
Home   >   mailing lists

Thread: division by zero issue

division by zero issue

From
Greg Donald
Date:
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/

Re: division by zero issue

From
Jean-Luc Lachance
Date:
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..
>

Re: division by zero issue

From
Guy Fraser
Date:
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..
>
>
>

Re: division by zero issue

From
Greg Donald
Date:
On Wed, 15 Sep 2004 12:55:24 -0400, Jean-Luc Lachance
<jllachan@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/

Re: division by zero issue

From
Peter Eisentraut
Date:
Greg Donald wrote:
> I get the error:
> aggregates not allowed in WHERE clause

Try HAVING then.

--
Peter Eisentraut
http://developer.postgresql.org/~petere/

Re: division by zero issue

From
David Fetter
Date:
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
> <jllachan@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 david@fetter.org http://fetter.org/
phone: +1 510 893 6100   mobile: +1 415 235 3778

Remember to vote!

Re: division by zero issue

From
Tom Lane
Date:
Greg Donald <destiney@gmail.com> writes:
> On Wed, 15 Sep 2004 12:55:24 -0400, Jean-Luc Lachance
> <jllachan@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

Re: division by zero issue

From
Greg Donald
Date:
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/

Re: division by zero issue

From
"Magnus Hagander"
Date:
> 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.

You're missing an END:

> SELECT
>   tasks.task_id,
>   CASE
>     WHEN task_count = '0'
>     THEN '0'::int4
>     ELSE (task_duration * task_duration_type / task_count) END as
                                                           ^^^^^^^

> hours_allocated
>   END
> FROM
...


//Magnus

Re: division by zero issue

From
David Fetter
Date:
On Wed, Sep 15, 2004 at 01:36:27PM -0500, Greg Donald wrote:
> 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

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 david@fetter.org http://fetter.org/
phone: +1 510 893 6100   mobile: +1 415 235 3778

Remember to vote!

Re: division by zero issue

From
Alvaro Herrera
Date:
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?"

Re: division by zero issue

From
Guy Fraser
Date:
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 <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..
>
>
>

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

Re: division by zero issue

From
Greg Donald
Date:
On Wed, 15 Sep 2004 12:54:07 -0600, Guy Fraser <guy@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/

Re: division by zero issue

From
Chester Kustarz
Date:
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
 ;

Re: division by zero issue

From
Chester Kustarz
Date:
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."

Re: division by zero issue

From
Martijn van Oosterhout
Date:
On Wed, Sep 15, 2004 at 02:10:45PM -0500, Greg Donald wrote:
> On Wed, 15 Sep 2004 12:54:07 -0600, Guy Fraser <guy@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   <kleptog@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.
Attachment

Re: division by zero issue

From
Gaetano Mendola
Date:
Greg Donald wrote:
> 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.

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

Re: division by zero issue

From
Gaetano Mendola
Date:
Gaetano Mendola wrote:
> Greg Donald wrote:
>
>> 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.
>
>
> 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