postgresql - 结合 SUM 和 CAST - 不起作用？

Question

PostgreSQL Unicode 9.01 不喜欢:

SELECT table1.fielda,
SUM (CAST (table2.fielda AS INT)) AS header.specific
FROM *etc*

SUM-CAST 有什么问题？

错误信息:

Incorrect column expression: 'SUM (CAST (specifics_nfl_3pl_work_order_item.delivery_quantity AS INT))

查询:

SELECT specifics_nfl_3pl_work_order.work_order_number,
specifics_nfl_3pl_work_order.goods_issue_date,
specifics_nfl_3pl_work_order.order_status_id,
SUM (CAST (specifics_nfl_3pl_work_order_item.delivery_quantity AS INT)) AS units
FROM public.specifics_nfl_3pl_work_order specifics_nfl_3pl_work_order,
public.specifics_nfl_3pl_work_order_item specifics_nfl_3pl_work_order_item,
public.specifics_nfl_order_status specifics_nfl_order_status
WHERE specifics_nfl_3pl_work_order.order_status_id In (3,17,14) 
AND specifics_nfl_3pl_work_order_item.specifics_nfl_work_order_id=
specifics_nfl_3pl_work_order.id 
AND ((specifics_nfl_3pl_work_order.sold_to_id<>'0000000000') 
AND (specifics_nfl_3pl_work_order.goods_issue_date>={d '2013-08-01'}))

如果您能提供帮助，那就太好了。

Answer 1

如果我是你，那么我会执行以下步骤:

• 给你的表取个短别名
• 格式化查询
• 使用正确的 ANSI 连接:
• 去掉函数名和(

---

select
    o.work_order_number,
    o.goods_issue_date,
    o.order_status_id,
    sum(cast(oi.delivery_quantity as int)) as units
from public.specifics_nfl_3pl_work_order as o
    inner join public.specifics_nfl_3pl_work_order_item as oi on
       oi.specifics_nfl_work_order_id = o.id
    -- inner join public.specifics_nfl_order_status os -- seems redundant
where
    o.order_status_id In (3,17,14) and
    o.sold_to_id <> '0000000000' and
    o.goods_issue_date >= {d '2013-08-01'}

实际上，我真的认为您需要在此处使用 group by 子句:

select
    o.work_order_number,
    o.goods_issue_date,
    o.order_status_id,
    sum(cast(oi.delivery_quantity as int)) as units
from public.specifics_nfl_3pl_work_order as o
    inner join public.specifics_nfl_3pl_work_order_item as oi on
       oi.specifics_nfl_work_order_id = o.id
where
    o.order_status_id In (3,17,14) and
    o.sold_to_id <> '0000000000' and
    o.goods_issue_date >= {d '2013-08-01'}
group by
    o.work_order_number,
    o.goods_issue_date,
    o.order_status_id

如果它仍然不起作用 - 尝试评论 sum 看看它是否起作用？