Oracle常见错误

ORA-00979: not a GROUP BY expression

当用到组函数时，出现在select列表中的字段，如果没有在组函数中，那么必须出现在group by子句中！
Cause:The GROUP BY clause does not contain all the expressions in the SELECT clause. SELECT expressions that are not included in a group function, such as AVG, COUNT, MAX, MIN, SUM, STDDEV, or VARIANCE, must be listed in the GROUP BY clause.
Action:Include in the GROUP BY clause all SELECT expressions that are not group function arguments.

ORA-00937: not a single-group group function

如果在select列表项中除了包含聚合函数外，还包含了表的某些列，那么你将必须使用group by语句。
Cause:A SELECT list cannot include both a group function, such as AVG, COUNT, MAX, MIN, SUM, STDDEV, or VARIANCE, and an individual column expression, unless the individual column expression is included in a GROUP BY clause.
Action:Drop either the group function or the individual column expression from the SELECT list or add a GROUP BY clause that includes all individual column expressions listed.


在where子句和group by子句中不允许使用聚合函数，否则会报：

ORA-00934: group function is not allowed here

Cause:One of the group functions, such as AVG, COUNT, MAX, MIN, SUM, STDDEV, or VARIANCE, was used in a WHERE or GROUP BY clause.
Action:Remove the group function from the WHERE or GROUP BY clause. The desired result may be achieved by including the function in a subquery or HAVING clause.
http://techonthenet.com/oracle/errors/ora00934.php

引用

Cause:You tried to execute an SQL statement that included one of the group functions (ie: MIN, MAX, SUM, COUNT) in either the WHERE clause or the GROUP BY clause.
Action:The options to resolve this Oracle error are:
1 Try removing the group function from the WHERE clause or GROUP BY clause. If required, you can move the group function to the HAVING clause.
For example, if you tried to execute the following SQL statement:

SELECT department, SUM(sales) as "Total sales"
FROM order_details
WHERE SUM(sales) > 1000
GROUP BY department;

You will receive the error "ORA-00934: group function is not allowed here"
You could correct this statement by using the HAVING clause as follows:

SELECT department, SUM(sales) as "Total sales"
FROM order_details
GROUP BY department
HAVING SUM(sales) > 1000;

2 You could also try moving the group by function to a subquery.
For example, if you tried to execute the following SQL statement:

SELECT department, SUM(sales) as "Total sales"
FROM order_details
WHERE SUM(sales) > 1000
GROUP BY department;

You will receive the error "ORA-00934: group function is not allowed here"
You could correct this statement by using a subquery as follows:

SELECT order_details.department,
SUM(order_details.sales) as "Total sales"
FROM order_details,	 (select department, SUM(sales) as Sales_compare
FROM order_details
GROUP BY department) subquery1
WHERE order_details.department = subquery1.department
AND subquery1.Sales_compare > 1000
GROUP BY order_details.department;

本博客相关解释：http://wuaner.iteye.com/blog/513099

引用

Illegal  Queries Using Group Functions:
- You cannot use the WHERE clause to restrict groups.  (即where子句中不允许使用聚合函数)
- You use the HAVING clause to restrict groups.(可以使用having子句为聚合函数加限定条件)
- You cannot use group functions in the WHERE clause.(即where子句中不允许使用聚合函数)

group by和order by的列可以不在select中；
用到了聚合函数并同时使用group by和order by时，select和order by中不在聚合函数里的columns必须都出现在group by中！
如，下面的写法会报“not a GROUP BY expression”：

select deptno, sum(sal) 
from emp 
group by deptno
order by deptno,job

正确的写法是：

select deptno, sum(sal) 
from emp 
group by deptno,job
order by deptno,job