Выражение не является выражением group by oracle
I'm having some serious issues for some days with this simple query :
INSERT INTO WM040P66 (CSTE,CETAB,CDEPOT,NUMINV,L17PAL,DPSECT,DPAGEO,CDART,VL,VA,LTNLOT,LTNLOS,TYPLI,REQP66)
SELECT CSTE, 'E10', CDEPOT, 1076, L17PAL, ' ', ' ', ' ', 0, 0, 0, 0, '3', 5 FROM V62FICFOR1.SM017V16
WHERE CSTE = 'OR10' AND CDEPOT = 'D11';
I have a "ORA-00979: not a GROUP BY expression" error when running this query, but if I remove the INSERT part, the SELECT query works fine.
This is an Oracle Database 12c Enterprise Edition Release 12.2.0.1.0 - 64bit Production. WM040P6 is a table and SM017V16 is a view. I also made sure every type matches.
I can't really say what the query is supposed to accomplish, this database is a huge mess, and I don't have time to learn how it all works. This query is from a software that I'm trying to debug (and this is an issue).
SM017V1 view definition :
CREATE OR REPLACE FORCE EDITIONABLE VIEW "V62FICFOR1"."SM017V16" ("CSTE", "CDEPOT", "L17PAL", "NBEMPL") AS
SELECT CSTE , CDEPOT , DPSECT , DPAGEO , L17PAL
WHERE L17PAL <> ' ' AND LTCANN = ' ' AND (LTQDIS + LTQRSR) <> 0
GROUP BY CSTE, L17PAL, CDEPOT, DPSECT, DPAGEO)
SELECT CSTE, CDEPOT, L17PAL, COUNT(*) AS NBEMPL
GROUP BY CSTE, CDEPOT, L17PAL
(I know there are 2 GROUP BY but the SELECT query works fine, so I don't think the issue is from this query. I don't see any error in this query too)
Any idea on what/where is the issue ?
9 Answers 9
You must put all columns of the SELECT in the GROUP BY or use functions on them which compress the results to a single value (like MIN , MAX or SUM ).
A simple example to understand why this happens: Imagine you have a database like this:
and you run SELECT * FROM table GROUP BY foo . This means the database must return a single row as result with the first column 0 to fulfill the GROUP BY but there are now two values of bar to chose from. Which result would you expect - A or B ? Or should the database return more than one row, violating the contract of GROUP BY ?
Or to put it another way: If you have two columns and group by the first, that means you'll have several values from the second column per result row. Since there is only a single result row but many values to choose from, which one should the DB return? The first it stumbles upon?
I partially agree. But assume the following case: there are 4 columns: A,B, C and D. Now I set (A,B,C) as a composite key. Then "select A,B,max(C), D . group by A, B" is not ambiguous, since for each combination of A,B and C, D is already defined. Still oracle refuses to do its job.
Include in the GROUP BY clause all SELECT expressions that are not group function arguments.
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.
BUT there is a work around for Oracle:
While the following line does not work
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)
@GlennFromIowa given my pseudo table isn't rigorously defined above, and that I not longer work for a firm with 11g, I can't provide a better example, though it was a problem when I last tested it.
Just out of curiosity what is an example of when you would want a random result? I can't think of any reason you would want to group by FOO and get a random row for BAR.
If you do grouping by virtue of including GROUP BY clause, any expression in SELECT , which is not group function (or aggregate function or aggregated column) such as COUNT , AVG , MIN , MAX , SUM and so on (List of Aggregate functions) should be present in GROUP BY clause.
Example (correct way) (here employee_id is not group function (non-aggregated column), so it must appear in GROUP BY . By contrast, sum(salary) is a group function (aggregated column), so it is not required to appear in the GROUP BY clause.
Example (wrong way) (here employee_id is not group function and it does not appear in GROUP BY clause, which will lead to the ORA-00979 Error .
Я не смог найти примеров, которые имели бы предложения GROUP BY и ORDER BY в одном запросе. Я попытался удалить каждое поле из группы по одному за раз, но все равно получаю ту же ошибку.
вы должны поместить все столбцы SELECT на GROUP BY или используйте на них функции, которые сжимают результаты до одного значения (например, MIN , MAX или SUM ).
простой пример, чтобы понять, почему это происходит: представьте, что у вас есть база данных, как это:
а ты беги SELECT * FROM table GROUP BY foo . Это означает, что база данных должна возвращать одну строку в результате с первым столбцом 0 выполнить GROUP BY но теперь есть два значения bar выбрать от. Какой результат вы ожидаете - A или B ? Или база данных должна возвращать более одной строки, нарушая контракт GROUP BY ?
входит в GROUP BY пункт все SELECT выражения, которые не являются аргументами групповой функции.
слишком плохо Oracle имеет такие ограничения. Конечно, результат для столбца не в GROUP BY будет случайным, но иногда вы этого хотите. Глупый Oracle, вы можете сделать это в MySQL / MSSQL.
но есть работа вокруг для Oracle:
пока следующая строка не работает
Вы можете обмануть Oracle с некоторыми 0, как показано ниже, чтобы сохранить ваш столбец в области, но не группировать его (предполагая, что это числа, в противном случае используйте Функция concat)
вы должны сделать следующее:
если вы делаете ощупью в силу включения GROUP BY статья, любое выражение в SELECT , который не является функцией группы (или агрегатной функцией или агрегированным столбцом), например COUNT , AVG , MIN , MAX , SUM и так далее (список агрегатных функций) должен присутствовать в GROUP BY предложения.
пример (правильный способ) (здесь employee_id не является групповой функцией (неагрегированный столбец), поэтому это должны явиться в GROUP BY . От напротив, sum (зарплата) - это групповая функция (агрегированный столбец), поэтому она не должна отображаться в GROUP BY предложения.
пример (неправильный путь) (здесь employee_id не является групповой функцией и не отображается в GROUP BY п., что приведет к на Ora-00979 ошибки .
- включить все неагрегированные выражения, перечисленные в SELECT статья в GROUP BY п.
- удалить функцию group (aggregate) из SELECT предложения.
group by используется для агрегирования некоторых данных, в зависимости от агрегатной функции, и кроме этого вам нужно поместить столбец или столбцы, к которым вам нужна группировка.
в результате в отделы максимальную зарплату.
теперь, если мы опустим d.deptno из предложения group by он даст ту же ошибку.
такая же ошибка также приходит, когда верхний или ниже ключевое слово не используется в обоих местах в select expression и group BY expression .
Whether you are an experienced SQL user or only a beginner, your SQL queries may return errors. The accompanying messages are supposed to help you fix them; however, sometimes the messages are not very helpful. The Oracle message “Not a GROUP BY expression” is one such example. Let’s review what this is about and how to fix it.
We are going to assume some basic understanding of how GROUP BY works. If you are not familiar with grouping data in SQL, check out this introductory article on using GROUP BY in SQL or go to our interactive SQL Basics course where you'll learn the foundations of SQL, including GROUP BY .
If you are familiar with GROUP BY , you should recall the following general rule: each column in the SELECT statement should either be called in an aggregate function or be in the GROUP BY clause.
ORA-00979 “ Not a GROUP BY expression ” is an error issued by the Oracle database when the SELECT statement contains a column that is neither listed in GROUP BY nor aggregated. This error message can be confusing to beginners.
Let’s review an example to understand why this error occurs. Consider the following table with basic information about each of our customers: ID, name, city, state, date of the last purchase, and the total amount of purchases.
Suppose we want to know the date of the last purchase and the average purchase amount per customer by state. We use the following query:
If you try to run this query, the Oracle database will output the following error message:
ORA-00979: not a GROUP BY expression
From this, it is not obvious what you need to fix. Other databases provide more understandable messages:
- PostgreSQL. ERROR: column "customers.city" must appear in the GROUP BY clause or be used in an aggregate function
- SQL Server. Column 'customers.city' is invalid in the select list because it is not contained in either an aggregate function or the GROUP BY clause.
So, what’s wrong with our query?
5 ответы
Вам нужно сделать одно из двух .
- GROUP BY и использование агрегатных функций для объединения нескольких записей в одну
- Используйте поиск, чтобы определить одну запись, которую вы хотите от группы
В вашем случае вам не просто нужен MAX () из table1, поскольку он май возможно, что более высокий id имеет более низкий date . В этом случае я был бы склонен использовать поисковую систему .
ПРИМЕЧАНИЕ: Предполагается, что ваша дата отформатирована таким образом, что буквенно-цифровой порядок БУДЕТ давать правильный порядок даты. Если это НЕ так (и d-m-yyyy будем не заказывать правильно) нужно заменить date_field с TO_DATE(date_field) для обеспечения правильного порядка.
ПРИМЕЧАНИЕ: использование TO_DATE(date_field) также, вероятно, исправит ваши проблемы с MAX ().
ПРИМЕЧАНИЕ: Если вы хотите хранить даты в виде строк, но чтобы они были удобными для порядка, используйте yyyy-mm-dd
ответ дан 26 окт '11, 12:10
Разве использование DISTINCT ID, MAX (date) select с группой по идентификатору не устраняет необходимость в row_number? - Том Хаббард
OP заявил, что MAX (дата) не будет работать, поскольку поле даты представляет собой строку в формате d-m-yyyy . По моей секунде ПРИМЕЧАНИЕ:, это означает, что MAX ( TO_DATE(date_field) ) необходим, чтобы он заработал. На этом этапе DISTINCT также больше не нужен. Однако преимущество ROW_NUMBER () заключается в том, что он более обобщен - если в таблице 1 есть другие неупорядоченные поля, он все равно их найдет. Если в реальной жизни действительно есть только id и date поля, тогда да, используя MAX(TO_DATE(date)) достаточно при использовании с GROUP BY table1.id, table2.id2, table2.val . - Мат Бейли
Я понимаю. Спасибо за разъяснения. - Том Хаббард
В предложении select и order by оператора SQL с group by вы можете использовать только столбцы / выражения, используемые в group by или агрегатных функциях (min, max, avg ..) других столбцов.
Why Does the Oracle Database Report This Error?
In the query above, we ask the database to show the following information about the customers:
- state,
- city,
- the last purchase date, and
- the total purchase amount.
Of these four columns, the state column appears in the GROUP BY clause, and the last_purchase_date and purchases columns are aggregated using MAX() and AVG() functions, respectively. The city column is neither called by an aggregate function nor is listed in GROUP BY . However, we have two cities in the state of California (“CA”) and two cities in the state of New York (“NY”). The database simply doesn’t know which value to display.
If you want to learn more about how GROUP BY works, check out my previous article, where I show how rows are grouped in SQL.
Answers
I suspect predicates are getting pushed into the view and that is causing aspects of the underlying view queries to be rewritten, the result is probably a combination of the two groupings, but where one of the columns isn't included. Although it could be an optimiser bug too, it's difficult to know, without your data and tables.
This part is effectively a distinct on those 5 columns.
SELECT CSTE , CDEPOT , DPSECT , DPAGEO , L17PAL
WHERE L17PAL <> ' ' AND LTCANN = ' ' AND (LTQDIS + LTQRSR) <> 0
GROUP BY CSTE, L17PAL, CDEPOT, DPSECT, DPAGEO
If you rewrite as the following, do you get the issue? The distinct may prevent any such issue (although performance may be different).
CREATE OR REPLACE FORCE EDITIONABLE VIEW "V62FICFOR1"."SM017V16" ("CSTE", "CDEPOT", "L17PAL", "NBEMPL") AS
SELECT DISTINCT CSTE , CDEPOT , DPSECT , DPAGEO , L17PAL
WHERE L17PAL <> ' ' AND LTCANN = ' ' AND (LTQDIS + LTQRSR) <> 0
SELECT CSTE, CDEPOT, L17PAL, COUNT(*) AS NBEMPL
GROUP BY CSTE, CDEPOT, L17PAL
Another version, that might be immune to your issue .
CREATE OR REPLACE FORCE EDITIONABLE VIEW "V62FICFOR1"."SM017V16" ("CSTE", "CDEPOT", "L17PAL", "NBEMPL") AS
SELECT CSTE, CDEPOT, L17PAL, COUNT(distinct DPSECT || chr(31) || DPAGEO) AS NBEMPL
WHERE L17PAL <> ' ' AND LTCANN = ' ' AND (LTQDIS + LTQRSR) <> 0
GROUP BY CSTE, CDEPOT, L17PAL
The chr(31) bit just makes sure columns are kept separate and overlaps aren't combined, in these sort of cases.
WHERE L17PAL <> ' ' AND LTCANN = ' ' AND (LTQDIS + LTQRSR) <> 0
should probably be written as
WHERE L17PAL is not null AND LTCANN is null AND (LTQDIS + LTQRSR) <> 0
I have tried to replicate the data
-- DDL for Table WM040P66
CREATE TABLE "WM040P66"
( "CSTE" VARCHAR2(10 BYTE),
"CETAB" VARCHAR2(10 BYTE),
"CDEPOT" VARCHAR2(10 BYTE),
"NUMINV" VARCHAR2(10 BYTE),
"L17PAL" VARCHAR2(10 BYTE),
"DPSECT" VARCHAR2(10 BYTE),
"DPAGEO" VARCHAR2(10 BYTE),
"CDART" VARCHAR2(10 BYTE),
"VL" VARCHAR2(10 BYTE),
"VA" VARCHAR2(10 BYTE),
"LTNLOT" VARCHAR2(10 BYTE),
"LTNLOS" VARCHAR2(10 BYTE),
"TYPLI" VARCHAR2(10 BYTE),
"REQP66" VARCHAR2(10 BYTE)
) SEGMENT CREATION IMMEDIATE
PCTFREE 10 PCTUSED 40 INITRANS 1 MAXTRANS 255 NOCOMPRESS LOGGING
STORAGE(INITIAL 65536 NEXT 1048576 MINEXTENTS 1 MAXEXTENTS 2147483645
PCTINCREASE 0 FREELISTS 1 FREELIST GROUPS 1 BUFFER_POOL DEFAULT FLASH_CACHE DEFAULT CELL_FLASH_CACHE DEFAULT)
Insert into WM040P66 (CSTE,CETAB,CDEPOT,NUMINV,L17PAL,DPSECT,DPAGEO,CDART,VL,VA,LTNLOT,LTNLOS,TYPLI,REQP66) values ('CSTE','E10','CDEPOT','1076','L17PAL',' ',' ',' ','0','0','0','0','3','5');
Insert into WM040P66 (CSTE,CETAB,CDEPOT,NUMINV,L17PAL,DPSECT,DPAGEO,CDART,VL,VA,LTNLOT,LTNLOS,TYPLI,REQP66) values ('CSTE','E10','CDEPOT','1076','L17PAL',' ',' ',' ','0','0','0','0','3','5');
Insert into WM040P66 (CSTE,CETAB,CDEPOT,NUMINV,L17PAL,DPSECT,DPAGEO,CDART,VL,VA,LTNLOT,LTNLOS,TYPLI,REQP66) values ('CSTE','E10','CDEPOT','1076','L17PAL',' ',' ',' ','0','0','0','0','3','5');
Insert into WM040P66 (CSTE,CETAB,CDEPOT,NUMINV,L17PAL,DPSECT,DPAGEO,CDART,VL,VA,LTNLOT,LTNLOS,TYPLI,REQP66) values ('CSTE','E10','CDEPOT','1076','L17PAL',' ',' ',' ','0','0','0','0','3','5');
-- DDL for Table SM017P10
CREATE TABLE "SM017P10"
( "CSTE" VARCHAR2(10 BYTE),
"CETAB" VARCHAR2(10 BYTE),
"CDEPOT" VARCHAR2(10 BYTE),
"NUMINV" VARCHAR2(10 BYTE),
"L17PAL" VARCHAR2(10 BYTE),
"DPSECT" VARCHAR2(10 BYTE),
"DPAGEO" VARCHAR2(10 BYTE),
"CDART" VARCHAR2(10 BYTE),
"VL" VARCHAR2(10 BYTE),
"VA" VARCHAR2(10 BYTE),
"LTNLOT" VARCHAR2(10 BYTE),
"LTNLOS" VARCHAR2(10 BYTE),
"TYPLI" VARCHAR2(10 BYTE),
"REQP66" VARCHAR2(10 BYTE),
"LTCANN" VARCHAR2(20 BYTE),
"LTQDIS" VARCHAR2(20 BYTE),
"LTQRSR" VARCHAR2(20 BYTE)
) SEGMENT CREATION IMMEDIATE
PCTFREE 10 PCTUSED 40 INITRANS 1 MAXTRANS 255 NOCOMPRESS LOGGING
STORAGE(INITIAL 65536 NEXT 1048576 MINEXTENTS 1 MAXEXTENTS 2147483645
PCTINCREASE 0 FREELISTS 1 FREELIST GROUPS 1 BUFFER_POOL DEFAULT FLASH_CACHE DEFAULT CELL_FLASH_CACHE DEFAULT)
Insert into SM017P10 (CSTE,CETAB,CDEPOT,NUMINV,L17PAL,DPSECT,DPAGEO,CDART,VL,VA,LTNLOT,LTNLOS,TYPLI,REQP66,LTCANN,LTQDIS,LTQRSR) values ('CSTE','E10','CDEPOT','1076','L17PAL',' ',' ',' ','0','0','0','0','3','5',null,null,null);
Insert into SM017P10 (CSTE,CETAB,CDEPOT,NUMINV,L17PAL,DPSECT,DPAGEO,CDART,VL,VA,LTNLOT,LTNLOS,TYPLI,REQP66,LTCANN,LTQDIS,LTQRSR) values ('CSTE','E10','CDEPOT','1076','L17PAL',' ',' ',' ','0','0','0','0','3','5',' ','0','0');
Insert into SM017P10 (CSTE,CETAB,CDEPOT,NUMINV,L17PAL,DPSECT,DPAGEO,CDART,VL,VA,LTNLOT,LTNLOS,TYPLI,REQP66,LTCANN,LTQDIS,LTQRSR) values ('CSTE','E10','CDEPOT','1076','L17PAL',' ',' ',' ','0','0','0','0','3','5',null,null,null);
Insert into SM017P10 (CSTE,CETAB,CDEPOT,NUMINV,L17PAL,DPSECT,DPAGEO,CDART,VL,VA,LTNLOT,LTNLOS,TYPLI,REQP66,LTCANN,LTQDIS,LTQRSR) values ('CSTE','E10','CDEPOT','1076','L17PAL',' ',' ',' ','0','0','0','0','3','5',' ','1',null);
Insert into SM017P10 (CSTE,CETAB,CDEPOT,NUMINV,L17PAL,DPSECT,DPAGEO,CDART,VL,VA,LTNLOT,LTNLOS,TYPLI,REQP66,LTCANN,LTQDIS,LTQRSR) values ('CSTE','E10','CDEPOT','1076','L17PAL',' ',' ',' ','0','0','0','0','3','5',' ',null,'1');
Insert into SM017P10 (CSTE,CETAB,CDEPOT,NUMINV,L17PAL,DPSECT,DPAGEO,CDART,VL,VA,LTNLOT,LTNLOS,TYPLI,REQP66,LTCANN,LTQDIS,LTQRSR) values ('CSTE','E10','CDEPOT','1076','L17PAL','1',' ',' ','0','0','0','0','3','5',' ','1','1');
Insert into SM017P10 (CSTE,CETAB,CDEPOT,NUMINV,L17PAL,DPSECT,DPAGEO,CDART,VL,VA,LTNLOT,LTNLOS,TYPLI,REQP66,LTCANN,LTQDIS,LTQRSR) values ('CSTE','E10','CDEPOT','1076','L17PAL',' ',' ',' ','0','0','0','0','3','5',' ',null,null);
Insert into SM017P10 (CSTE,CETAB,CDEPOT,NUMINV,L17PAL,DPSECT,DPAGEO,CDART,VL,VA,LTNLOT,LTNLOS,TYPLI,REQP66,LTCANN,LTQDIS,LTQRSR) values ('CSTE','E10','CDEPOT','1076','L17PAL','2',' ',' ','0','0','0','0','3','5',' ','1','1');
CREATE VIEW SM017V16 AS WITH TEMP AS
( SELECT CSTE , CDEPOT , DPSECT , DPAGEO , L17PAL
WHERE L17PAL <> ' ' AND LTCANN = ' ' AND (LTQDIS + LTQRSR) <> 0
GROUP BY CSTE , CDEPOT , DPSECT , DPAGEO , L17PAL
SELECT CSTE, CDEPOT, L17PAL, COUNT(*) AS NBEMPL
GROUP BY CSTE, CDEPOT, L17PAL
INSERT INTO WM040P66 (CSTE,CETAB,CDEPOT,NUMINV,L17PAL,DPSECT,DPAGEO,CDART,VL,VA,LTNLOT,LTNLOS,TYPLI,REQP66)
SELECT CSTE, 'E10', CDEPOT, 1076, L17PAL, ' ', ' ', ' ', 0, 0, 0, 0, '3', 5 FROM SM017V16
WHERE CSTE = 'CSTE' AND CDEPOT = 'CDEPOT';
Everything works well you should tables and data, try this example.
Oracle Database 11g Express Edition Release 11.2.0.2.0 - 64bit Production
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.
How to Fix the “Not a GROUP BY Expression” Error
Your approach to fixing this error will depend on what you want to achieve. Here are the options to consider.
Option 1. Add the city column to GROUP BY . If you want the city to be displayed in the output, you’ll need to group the data by both state and city .
Here is the result you’ll get when grouping by state and city :
state | city | last_purchase | avg_purchases |
---|---|---|---|
NY | Buffalo | 2020-05-25 | 435.00 |
CA | San Francisco | 2020-09-09 | 115.33 |
CA | Los Angeles | 2020-03-23 | 548.00 |
NY | New York | 2020-10-02 | 1287.75 |
Option 2. Remove the city column from SELECT . If you want your output to be grouped by state only, you’ll need to remove city from the SELECT statement. As I have demonstrated above, it is simply not possible to display the city when the rows are grouped only by state .
The result will be as follows:
state | last_purchase | avg_purchases |
---|---|---|
CA | 2020-09-09 | 288.40 |
NY | 2020-10-02 | 1003.50 |
Option 3. Call the city column in an aggregate function. In some cases, you may want to aggregate data in the column using a function such as COUNT() , SUM() , AVG() , MAX() , or MIN() .
In our example, we may choose to display how many unique cities with customers there are in each state:
Here’s what you’ll get when you run this query:
state | cities_with_customers | last_purchase | avg_purchases |
---|---|---|---|
CA | 2 | 2020-09-09 | 288.40 |
NY | 2 | 2020-10-02 | 1003.50 |
Check out this article for more examples of GROUP BY .
Fix the “Not a GROUP BY Expression” Error and Practice More GROUP BY!
- SQL Basics is an introductory SQL course that covers all basic topics, including grouping and aggregating data in SQL.
- SQL Practice Set is a set of over 80 SQL exercises to help you practice grouping and more in SQL.
- Creating Basic SQL Reports focuses on the details of GROUP BY not covered in the SQL Basics course. Here, you will learn about some common mistakes with GROUP BY and see how it is used in real-world reports.
Finally, now that you’re firmly grounded in the key rule of the GROUP BY clause – namely, all non-aggregated columns from the SELECT statement should be in GROUP BY – it turns out that this is not always the case! Intrigued? Read this article to learn more.
РЕДАКТИРОВАТЬ: MAX функция на столбце Date не работает, потому что этот столбец varchar(200) Структура базы данных / таблиц не моя, и я не могу ее изменить.
Разве это не должно быть WHERE table1.ID = table2.ID2 ? - Xavi López
Ага, извини, что забыл написать !! - Marcx
Читайте также: