SQL ORA-00979 不是按表达式分组

ORA-00979 not a group by expression

提问by Theresa

I am getting ORA-00979 with the following query:

我通过以下查询获得 ORA-00979：

SELECT cr.review_sk, cr.cs_sk, cr.full_name,
tolist(to_char(cf.fact_date, 'mm/dd/yyyy')) "appt",
cs.cs_id, cr.tracking_number
from review cr, cs, fact cf
where cr.cs_sk = cs.cs_sk
and UPPER(cs.cs_id) like '%' || UPPER(i_cs_id) || '%'
and row_delete_date_time is null
and cr.review_sk = cf.review_wk (+)
and cr.fact_type_code (+) = 183050
GROUP BY cr.review_sk, cr.cs_sk, cf.fact_date, cr.tracking_number
ORDER BY cs.cs_id, cr.full_name;

I couldn't find any examples that had both GROUP BY and ORDER BY clauses in the same query. I tried removing each field from the group by one at a time, but am still getting the same error.

我找不到在同一查询中同时包含 GROUP BY 和 ORDER BY 子句的任何示例。我尝试一次从组中删除每个字段，但仍然遇到相同的错误。

回答by Aaron Digulla

You must put all columns of the SELECTin the GROUP BYor use functions on them which compress the results to a single value (like MIN, MAXor SUM).

你必须把的所有列SELECT的GROUP BY或使用的压缩结果为单一值（如对他们的功能MIN，MAX或SUM）。

A simple example to understand why this happens: Imagine you have a database like this:

一个简单的例子来理解为什么会发生这种情况：想象一下你有一个这样的数据库：

FOO BAR
0   A
0   B

and you run SELECT * FROM table GROUP BY foo. This means the database must return a single row as result with the first column 0to fulfill the GROUP BYbut there are now two values of barto chose from. Which result would you expect - Aor B? Or should the database return more than one row, violating the contract of GROUP BY?

然后你跑SELECT * FROM table GROUP BY foo。这意味着数据库必须返回一行作为结果的第一列0来满足，GROUP BY但现在有两个值bar可供选择。您期望哪个结果 -A或者B？或者数据库应该返回多于一行，违反GROUP BY?

回答by Xaisoft

Include in the GROUP BYclause all SELECTexpressions that are not group function arguments.

在GROUP BY子句中包括所有SELECT不是组函数参数的表达式。

回答by Joseph Lust

Too bad Oracle has limitations like these. Sure, the result for a column not in the GROUP BY would be random, but sometimes you want that. Silly Oracle, you can do this in MySQL/MSSQL.

太糟糕了 Oracle 有这些限制。当然，不在 GROUP BY 中的列的结果是随机的，但有时您需要这样。愚蠢的甲骨文，你可以在 MySQL/MSSQL 中做到这一点。

BUT there is a work around for Oracle:

但是 Oracle 有一个解决方法：

While the following line does not work

虽然以下行不起作用

SELECT unique_id_col, COUNT(1) AS cnt FROM yourTable GROUP BY col_A;

You can trick Oracle with some 0's like the following, to keep your column in scope, but not group by it (assuming these are numbers, otherwise use CONCAT)

您可以使用如下所示的一些 0 来欺骗 Oracle，以将您的列保持在范围内，但不能对其进行分组（假设这些是数字，否则使用 CONCAT）

SELECT MAX(unique_id_col) AS unique_id_col, COUNT(1) AS cnt 
FROM yourTable GROUP BY col_A, (unique_id_col*0 + col_A);

回答by fg78nc

If you do groping by virtue of including GROUP BYclause, any expression in SELECT, which is not group function (or aggregate function or aggregated column) such as COUNT, AVG, MIN, MAX, SUMand so on (List of Aggregate functions) should be present in GROUP BYclause.

如果借助于包括摸索GROUP BY子句，在任何表达式SELECT，这是不组函数（或集合函数或聚集的列），例如COUNT，AVG，MIN，MAX，SUM等（的集合函数列表）应存在于GROUP BY子句。

Example (correct way)(here employee_idis not group function (non-aggregated column), so it mustappear in GROUP BY. By contrast, sum(salary) is a group function (aggregated column), so it is not required to appear in the GROUP BYclause.

例子（正确方式）（这里employee_id不是group function（非聚合列），所以必须出现在GROUP BY.中。相比之下，sum(salary)是group function（聚合列），所以不需要出现在GROUP BY子句中.

   SELECT employee_id, sum(salary) 
   FROM employees
   GROUP BY employee_id; 

Example (wrong way)(here employee_idis not group function and it does not appear in GROUP BYclause, which will lead to the ORA-00979 Error .

示例（错误方式）（这里employee_id不是组函数，也没有出现在GROUP BY子句中，会导致 ORA-00979 错误。

   SELECT employee_id, sum(salary) 
   FROM employees;

To correct you need to do oneof the following :

要更正，您需要执行以下操作之一：

• Include all non-aggregated expressions listed in SELECTclause in the GROUP BYclause
• Remove group (aggregate) function from SELECTclause.

• 在其中列出所有的非聚集表达式SELECT的条款 GROUP BY条款
• 从SELECT子句中删除组（聚合）函数。

回答by Pavel Zimogorov

You should do the following:

您应该执行以下操作：

SELECT cr.review_sk, 
       cr.cs_sk, 
       cr.full_name,
       tolist(to_char(cf.fact_date, 'mm/dd/yyyy')) "appt",
       cs.cs_id, 
       cr.tracking_number
from review cr, cs, fact cf
where cr.cs_sk = cs.cs_sk
       and UPPER(cs.cs_id) like '%' || UPPER(i_cs_id) || '%'
       and row_delete_date_time is null
       and cr.review_sk = cf.review_wk (+)
       and cr.fact_type_code (+) = 183050
GROUP BY cr.review_sk, cr.cs_sk, cf.fact_date, cr.tracking_number, cs.cs_id, cr.full_name
ORDER BY cs.cs_id, cr.full_name;

回答by Opster Elasticsearch Pro-Vijay

Same error also come when UPPERor LOWERkeyword not used in both place in select expression and group by expression .

当UPPER或LOWER关键字未在 select expression 和 group by expression 中的位置使用时，也会出现相同的错误。

Wrong :-

错误的 ：-

select a , count(*) from my_table group by UPPER(a) .

Right :-

对 ：-

select UPPER(a) , count(*) from my_table group by UPPER(a) .

回答by Bjorks number one fan

In addition to the other answers, this error can result if there's an inconsistency in an order by clause. For instance:

除了其他答案之外，如果 order by 子句中存在不一致，则可能会导致此错误。例如：

select 
    substr(year_month, 1, 4)
    ,count(*) as tot
from
    schema.tbl
group by
    substr(year_month, 1, 4)
order by
    year_month

回答by Muhammad Nadeem

The group by is used to aggregate some data, depending on the aggregate function, and other than that you need to put column or columns to which you need the grouping.

group by 用于聚合一些数据，具体取决于聚合函数，除此之外，您需要将需要分组的列放在一个或多个列中。

for example:

例如：

select d.deptno, max(e.sal) 
from emp e, dept d
where e.deptno = d.deptno
group by d.deptno;

This will result in the departments maximum salary.

这将导致部门最高工资。

Now if we omit the d.deptnofrom group by clause it will give the same error.

现在，如果我们省略d.deptnofrom group by 子句，它将给出相同的错误。