问题描述
HI
CAN ANY BODY EXPLAIN IN 1ST QUERY WHY IT IS ERROR BUT IN 2ND QUERY IT GIVES RESULT ?
SQL> SELECT SUM(SAL),
2 (
3 SELECT SUM(SAL) FROM EMP
4 )
5 FROM EMP;
SELECT SUM(SAL) FROM EMP
*
ERROR at line 3:
ORA-00937: not a single-group group function
SQL> ED
Wrote file afiedt.buf
1 SELECT SUM(SAL),
2 (
3 SELECT SUM(SAL) FROM EMP
4 )
5 FROM EMP
6* GROUP BY DEPTNO
SQL> /
9400.00 29025.00
10875.00 29025.00
8750.00 29025.00
专家解答
MOS note 1670412.1解决了类似的问题:
SYMPTOMS
The following query fails with ora-00937 when it look like there is no need for a group by function
SELECT
CASE
WHEN (COUNT(*) > 0)
THEN '1'
ELSE '0'
END ,
(SELECT 1 FROM DUAL)
FROM test T1 ;
(SELECT 1 FROM DUAL)
*
ERROR at line 7:
ORA-00937: not a single-group group function
CAUSE
This problem is due to the aggregate expression (COUNT(*) > 0) being used together with a sub-query and failing during parse as we generally need to have a matching GROUP BY clause for aggregates.
The ORA-937 is seen when we combine the aggregate with a sub-query, in which case the parser will require the GROUP BY clause to process the SQL.
Generally we should have GROUP BY present when using aggregates
SOLUTION
Adding a GROUP BY function on any column in the table will solve the error
SQL>SELECT
CASE
WHEN (COUNT(col1) > 0)
THEN '1'
ELSE '0'
END ,
CASE
WHEN EXISTS (SELECT 1 FROM DUAL)
THEN '1'
ELSE '0'
END
FROM test T1
GROUP BY T1.col2;
所以这里的 “修复” 是添加一个group by子句
或者，更好的是，如果你想所有的行从emp与总工资沟子查询。并在sum之后添加0ver () 子句:
SYMPTOMS
The following query fails with ora-00937 when it look like there is no need for a group by function
SELECT
CASE
WHEN (COUNT(*) > 0)
THEN '1'
ELSE '0'
END ,
(SELECT 1 FROM DUAL)
FROM test T1 ;
(SELECT 1 FROM DUAL)
*
ERROR at line 7:
ORA-00937: not a single-group group function
CAUSE
This problem is due to the aggregate expression (COUNT(*) > 0) being used together with a sub-query and failing during parse as we generally need to have a matching GROUP BY clause for aggregates.
The ORA-937 is seen when we combine the aggregate with a sub-query, in which case the parser will require the GROUP BY clause to process the SQL.
Generally we should have GROUP BY present when using aggregates
SOLUTION
Adding a GROUP BY function on any column in the table will solve the error
SQL>SELECT
CASE
WHEN (COUNT(col1) > 0)
THEN '1'
ELSE '0'
END ,
CASE
WHEN EXISTS (SELECT 1 FROM DUAL)
THEN '1'
ELSE '0'
END
FROM test T1
GROUP BY T1.col2;
所以这里的 “修复” 是添加一个group by子句
select sum( sal ),
(select sum( sal )
from scott.emp
)
from scott.emp;
SQL Error: ORA-00937: not a single-group group function
select sum( sal ),
(select sum( sal )
from scott.emp
)
from scott.emp
group by 1;
SUM(SAL) (SELECTSUM(SAL)FROMSCOTT.EMP)
29025 29025 或者，更好的是，如果你想所有的行从emp与总工资沟子查询。并在sum之后添加0ver () 子句:
select sum( sal ) over () from scott.emp; SUM(SAL)OVER() 29025 29025 29025 29025 29025 29025 29025 29025 29025 29025 29025 29025 29025 29025
「喜欢这篇文章，您的关注和赞赏是给作者最好的鼓励」
关注作者
【版权声明】本文为墨天轮用户原创内容，转载时必须标注文章的来源（墨天轮），文章链接，文章作者等基本信息，否则作者和墨天轮有权追究责任。如果您发现墨天轮中有涉嫌抄袭或者侵权的内容，欢迎发送邮件至：contact@modb.pro进行举报，并提供相关证据，一经查实，墨天轮将立刻删除相关内容。
