对于每个拥有5名以上员工的分支机构，请查找每个分支机构中的产品数量及其购买总和。
SELECT SUM(ORDERS.PRODUCT_NO) AS Products_Sold ,
SUM(ORDERLINE.ORDERLINE_NO) AS Total_Purchases, SUM(EMPLOYEE.EMPLOYEE_NO)
FROM BRANCH JOIN ORDERS
ON BRANCH.BRANCH_NO = ORDERS.BRANCH_NO
JOIN ORDERLINE
ON ORDERS.ORDER_NO = ORDERLINE.ORDER_NO
JOIN EMPLOYEE
ON BRANCH.BRANCH_NO = EMPLOYEE.BRANCH_NO
GROUP BY (SUM(ORDERS.PRODUCT_NO), SUM(ORDERLINE.ORDERLINE_NO),
SUM(EMPLOYEE.EMPLOYEE_NO))
HAVING SUM(EMPLOYEE_NO) >= 5;
我得到这个错误
ORA-00934: group function is not allowed here
00934. 00000 - "group function is not allowed here"
*Cause:
*Action:
Error at Line: 19 Column: 10
最佳答案
SELECT SUM(ORDERLINE.PRODUCT_NO) AS Products_Sold , SUM(ORDERLINE.ORDERLINE_NO) AS Total_Purchases
FROM BRANCH JOIN ORDERS
ON BRANCH.BRANCH_NO = ORDERS.BRANCH_NO
JOIN ORDERLINE
ON ORDERS.ORDER_NO = ORDERLINE.ORDER_NO
JOIN EMPLOYEE
ON BRANCH.BRANCH_NO = EMPLOYEE.BRANCH_NO
GROUP BY ORDERLINE.PRODUCT_NO, ORDERLINE.ORDERLINE_NO, EMPLOYEE.EMPLOYEE_NO
HAVING SUM(EMPLOYEE_NO) >= 5;