postgresql - Combine SUM and CAST - not working? -

postgresql unicode 9.01 doesn't like:

select table1.fielda, sum (cast (table2.fielda int)) header.specific *etc* 

what wrong sum-cast?

error message:

incorrect column expression: 'sum (cast (specifics_nfl_3pl_work_order_item.delivery_quantity int))

query:

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 int)) units 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 specifics_nfl_3pl_work_order.order_status_id in (3,17,14)  , specifics_nfl_3pl_work_order_item.specifics_nfl_work_order_id= specifics_nfl_3pl_work_order.id  , ((specifics_nfl_3pl_work_order.sold_to_id<>'0000000000')  , (specifics_nfl_3pl_work_order.goods_issue_date>={d '2013-08-01'})) 

it great if can help.

if you, these steps:

• give table short aliases
• format query
• use proper ansi joins:
• remove spaces between function name , (

---

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

actually think need group clause here:

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

if still doesn't work - try comment sum , see working?