按零问题划分 [英] division by zero issue

问题描述

将一些MySQL代码转换为与Postgres一起使用。


我有这个查询：


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

;


问题是有时count（user_tasks.task_id）等于零，

所以我得到除零错误。是否有一种简单的方法可以使

部分查询无声地失败并且等于零而不是

除以产生错误？


TIA ..


-

Greg Donald
http://gdconsultants.com/
http://destiney.com/


------------------------- - （广播结束）---------------------------

提示9：规划人员会忽略你的如果您的

加入列的数据类型不匹配，则选择索引扫描的愿望

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

推荐答案

添加：


AND count（user_tasks.task_id）>在where子句中为0。


Greg Donald写道：

Add :

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

Greg Donald wrote:

将一些MySQL代码转换为与Postgres一起使用。

tasks.task_id，
（tasks.task_duration * tasks.task_duration_type /
count（user_tasks.task_id））为hours_allocated
FROM任务
LEFT JOIN user_tasks
ON tasks.task_id = user_tasks.task_id
WHERE tasks.task_milestone =''0''
GROUP BY
任务.task_id，
task_duration，
task_duration_type
;

问题是有时count（user_tasks.task_id）等于零，
所以我得到了除法零错误。是否有一种简单的方法可以使查询的一部分无声地失败并且等于零而不是分割并产生错误？

TIA ..

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

---------------------------（广播结束）------- --------------------

提示4：不要杀死-9''邮政局长

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

也许尝试这样的事情：


SELECT

task_id，

案例

WHEN task_count =''0''

那么'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任务

LEFT JOIN user_tasks

ON tasks.task_id = user_tasks.task_id

WHERE tasks.task_milestone =''0''

GRO UP BY

tasks.task_id，

task_duration，

task_duration_type

）作为中间人

;

这是在袖口完成的，所以它可能无法正常工作。


Greg Donald写道：

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:

转换一些MySQL代码在这里与Postgres一起工作。

我有这样的疑问：

tasks
tasks.task_id，
（任务。 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任务.task_milestone =''0''
GROUP BY
tasks.task_id，
task_duration，
task_duration_type
;

问题在于有时count（user_tasks.task_id）等于零，
所以我得到除零错误。是否有一种简单的方法可以使查询的一部分无声地失败并且等于零而不是分割并产生错误？

TIA ..

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

---------------------------（广播结束）------- --------------------

提示1：订阅和取消订阅命令转到 ma ******* @ postgresql.org

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

2004年9月15日星期三12 ：55：24 -0400，Jean-Luc Lachance

< jl ****** @ sympatico.ca>写道：

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

添加：

AND count（user_tasks.task_id）>在where子句中为0。

Add :

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

我收到错误：

WHERE子句中不允许聚合

- -

Greg Donald
http://gdconsultants.com/
http://destiney.com/

---------------------------（广播结束）------------- --------------

提示7：别忘了增加免费空间地图设置

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

这篇关于按零问题划分的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！