sql - SQL 错误 [937] [42000]: ORA-00937: not a single-group group function
问题描述
我使用oracle用CTE计算一些别名,但是当我运行它时发生错误
这是我的查询:
WITH BOXCOUNT AS (
SELECT
PRODUCT_CODE
, ROUND(SUM(TOTAL_QUANTITY/PACKING_STYLE)) AS FULLBOX
, CASE WHEN SUM((TOTAL_QUANTITY - ROUND(SUM(TOTAL_QUANTITY/PACKING_STYLE))) * PACKING_STYLE) = 0 THEN 0
WHEN SUM((TOTAL_QUANTITY - ROUND(SUM(TOTAL_QUANTITY/PACKING_STYLE))) * PACKING_STYLE) > 0 THEN 1
END AS SPAREBOX
FROM
LOG0104M
GROUP BY
PRODUCT_CODE)
SELECT
L04.PRODUCT_CODE
, L04.PRODUCT_NAME
, L04.TOTAL_QUANTITY
, L04.PACKING_STYLE
, BC.FULLBOX
, BC.SPAREBOX
, SUM(BC.FULLBOX + BC.SPAREBOX) AS TOTALBOX
FROM LOG0104M L04
JOIN BOXCOUNT BC
ON BC.PRODUCT_CODE = L04.PRODUCT_CODE
GROUP BY
L04.PRODUCT_CODE
, L04.PRODUCT_NAME
, L04.TOTAL_QUANTITY
, L04.PACKING_STYLE
, BC.FULLBOX
, BC.SPAREBOX
这是一个错误:
SQL Error [937] [42000]: ORA-00937: not a single-group group function
我该如何解决这个错误
解决方案
请在 CTE 中更改以下代码行
CASE WHEN SUM((TOTAL_QUANTITY - ROUND(SUM(TOTAL_QUANTITY/PACKING_STYLE))) * PACKING_STYLE) = 0 THEN 0
WHEN SUM((TOTAL_QUANTITY - ROUND(SUM(TOTAL_QUANTITY/PACKING_STYLE))) * PACKING_STYLE) > 0 THEN 1
END AS SPAREBOX
按功能分组应应用于 CASE 表达式,即
sum(CASE WHEN ((TOTAL_QUANTITY - ROUND((TOTAL_QUANTITY/PACKING_STYLE))) * PACKING_STYLE) = 0 THEN 0
WHEN ((TOTAL_QUANTITY - ROUND((TOTAL_QUANTITY/PACKING_STYLE))) * PACKING_STYLE) > 0 THEN 1
END) AS SPAREBOX