sql错误1111 - 无效使用组功能

I'm getting this error while using below script, I've searched many threads in this site and google but I can't find the solution for my problem.

我在使用下面的脚本时遇到这个错误,我搜索了这个网站和谷歌的很多线程,但我找不到我的问题的解决方案。

    select ct.tons as total_tons,
       sum(IF(sum(wr.tons) is not null,sum(wr.tons),0)) as total_delivered,            
       sum(ct.tons - IF(sum(wr.tons) is not null,sum(wr.tons),0)) as total_balance,
             sum(IF(sum(f.tons_fixed) is not null, sum(f.tons_fixed), 0)) as total_fixed,
             sum(IF(sum(f.tons_fixed) is not null, ct.tons - sum(f.tons_fixed), 0)) as total_unfixed,
             avg(sum(IF(f.tons_fixed * f.hedge_price is not null,f.tons_fixed * f.hedge_price, 0))/IF(sum(f.tons_fixed) is not null, sum(f.tons_fixed), 1) + ct.differential_fob) as avg_contract_price,
           avg(db.current_basis) as avg_market_price,            
             avg(ct.contract_price_foreign - db.current_basis) as avg_outright_fob,
             avg(ct.differential_fob) as avg_contract_fob_diff,
             avg(IF(mf.diff is not null, mf.diff, 0)) as avg_market_fob,
             avg(ct.differential_fob - IF(mf.diff is not null, mf.diff, 0)) as avg_diff_fob 
from contract ct 
left join grade_master gm on ct.grade_id = gm.id
left join movement m on m.contract_id = ct.id
left join warehouse_receipt wr on wr.movement_id = m.id
left join daily_basis db on ct.terminal_month = db.id 
left join contract_price_fixation f on f.contract_id = ct.id
left join market_fob_diff mf on mf.grade_id = ct.grade_id
left join grade_master gmt on mf.grade_id = gmt.id
left join company_master cm on cm.id = ct.supplier_buyer_id
GROUP BY ct.id

I think the problem could be sum within sum (avg), because when removing the leftmost sum, the script worked. please help me to correct it!
thanks

我认为问题可能是sum(avg)中的总和,因为当删除最左边的总和时,脚本可以工作。请帮我纠正一下!谢谢

1 个解决方案

#1

Change lines like this

改变这样的行

sum(IF(sum(wr.tons) is not null,sum(wr.tons),0)) as total_delivered

COALESCE(SUM(wr.tons), 0) AS total_delivered

The COALESCE() returns the first of it's parameters which is not null.

COALESCE()返回其中第一个非空的参数。

#1