Group by Clause错误，而不是Group by表达式

Question

我有以下代码，

Select
pro.tariff_code as CommodityCode,
'1000001' AS ProcedureCode,
pro.long_description_1 as Description,
sum(ph.weight + 0.2) as GrossWeight,
sum(ph.weight) as NetWeight,
sum(Si.net_price) as ItemUnitPrice,
ed.data_text as Countryoforigin,
sum(si.despatched_qty) as ItemQuantity,
'PC' as Packagedcode,
'LIC99' as Licence
from 
package_header ph
left join despatch_header dc on ph.despatch_num = dc.despatch_num
left join package_product pp on ph.despatch_num = pp.despatch_num
left join sales_header sh on pp.sales_document_num = sh.sales_document_num
left join sales_item si on sh.sales_document_num = si.sales_document_num
left join product pro on si.product_code = pro.product_code
left join entity_data ed on field_name = 'CountryOfOrigin' and Entity_name = 'Product' and entity_key1 = pro.product_code
WHERE trunc(date '1970-01-01' + ph.change_date * interval '1' second) = trunc(sysdate) and sh.SALES_OFFICE = 'MSUK'
Group by pro.tariff_code, '1000001', pro.long_description_1

我在说时出错

ORA-00979：不是GROUP BY表达式

感谢您的帮助。

Answer 1

SELECT子句的所有非聚合列必须在group by子句中重复。您的代码在group by子句中缺少ed.data_text，或者应该将其从select子句中删除。

select
pro.tariff_code as CommodityCode,
'1000001' AS ProcedureCode,
pro.long_description_1 as Description,
sum(ph.weight + 0.2) as GrossWeight,
sum(ph.weight) as NetWeight,
sum(Si.net_price) as ItemUnitPrice,
ed.data_text as Countryoforigin,
sum(si.despatched_qty) as ItemQuantity,
'PC' as Packagedcode,
'LIC99' as Licence
from 
package_header ph
left join despatch_header dc on ph.despatch_num = dc.despatch_num
left join package_product pp on ph.despatch_num = pp.despatch_num
left join sales_header sh on pp.sales_document_num = sh.sales_document_num
left join sales_item si on sh.sales_document_num = si.sales_document_num
left join product pro on si.product_code = pro.product_code
left join entity_data ed on field_name = 'CountryOfOrigin' and Entity_name = 'Product' and entity_key1 = pro.product_code
WHERE ph.change_date >= (trunc(sysdate) - date '1970-01-01') * 60 * 60 * 24
AND ph.change_date <  (trunc(sysdate) - date '1970-01-01' + 1) * 60 * 60 * 24
AND sh.SALES_OFFICE = 'MSUK'
Group by pro.tariff_code, pro.long_description_1, ed.data_text

请注意，我更改了日期的过滤，使其可以SARGeable。