I am getting ORA-00979 with the following query:
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.
Ben
51.1k36 gold badges127 silver badges148 bronze badges
asked Oct 5, 2009 at 14:57
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:
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 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?
answered Oct 5, 2009 at 15:02
Aaron DigullaAaron Digulla
317k106 gold badges588 silver badges812 bronze badges
8
Include in the GROUP BY clause all SELECT expressions that are not group function arguments.
Ram Sharma
8,6167 gold badges44 silver badges56 bronze badges
answered Oct 5, 2009 at 15:01
XaisoftXaisoft
45.1k87 gold badges278 silver badges429 bronze badges
0
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
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)
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);
Peter O.
31.7k14 gold badges78 silver badges93 bronze badges
answered Apr 26, 2011 at 18:26
Joseph LustJoseph Lust
18.9k7 gold badges81 silver badges76 bronze badges
3
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 BYclause.
SELECT employee_id, sum(salary)
FROM employees
GROUP BY employee_id;
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 .
SELECT employee_id, sum(salary)
FROM employees;
To correct you need to do one of the following :
- Include all non-aggregated expressions listed in
SELECTclause in the
GROUP BYclause - Remove group (aggregate) function from
SELECTclause.
BobRodes
5,9062 gold badges24 silver badges26 bronze badges
answered Jul 11, 2017 at 6:49
fg78ncfg78nc
4,5883 gold badges18 silver badges30 bronze badges
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;
answered Apr 19, 2016 at 6:28
Same error also come when UPPER or LOWER keyword not used in both place in select expression and 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) .
answered Mar 23, 2017 at 10:52
VijayVijay
4,5561 gold badge28 silver badges37 bronze badges
In addition to the other answers, this error can result if there’s an inconsistency in an order by clause. For instance:
select
substr(year_month, 1, 4)
,count(*) as tot
from
schema.tbl
group by
substr(year_month, 1, 4)
order by
year_month
answered Feb 11, 2019 at 14:57
3pitt3pitt
85912 silver badges21 bronze badges
Adding an alternative solution for @Joseph Lust’s answer regarding random rows in each group. This is made possible in Oracle Database 19c or newer by using any_value keyword. Here is an example:
select customer_id,
max ( order_datetime ),
any_value ( store_id ),
any_value ( order_status ),
any_value ( order_id )
from co.orders
group by customer_id;
answered Oct 18, 2022 at 7:49
Yodi S.Yodi S.
1061 silver badge5 bronze badges
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.
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.deptno from group by clause it will give the same error.
cнŝdk
31k7 gold badges56 silver badges76 bronze badges
answered Apr 7, 2016 at 7:26
Muhammad NadeemMuhammad Nadeem
3604 gold badges7 silver badges21 bronze badges
The answer of «Aaron Digulla» (the first at this time) inspired my solution for the same error code using Spring Boot 2 (JPA / Hibernate) and CriteriaQuery / CriteriaBuilder.
Make a List of selects, and add it to your criteriaQuery.multiselect()
List<Selection> selects = new ArrayList<>();
selects.add(seccionRoot.get("id"));
selects.add(synSeccionRoot.get("DDF"));
selects.add(synSeccionRoot.get("TTYU"));
selects.add(synSeccionRoot.get("4567"));
selects.add(seccionRoot.get("22").get("223"));
selects.add(tasaRoot.get("price"));
selects.add(tasaRoot.get("chair"));
cq.multiselect(selects.toArray(Selection[]::new));
Then you can cast the List to an Expression[]
cq.groupBy(selects.toArray(Expression[]::new));
answered Apr 27, 2022 at 14:46
ieselisraieselisra
3874 silver badges16 bronze badges
I am getting ORA-00979 with the following query:
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.
Ben
51.1k36 gold badges127 silver badges148 bronze badges
asked Oct 5, 2009 at 14:57
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:
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 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?
answered Oct 5, 2009 at 15:02
Aaron DigullaAaron Digulla
317k106 gold badges588 silver badges812 bronze badges
8
Include in the GROUP BY clause all SELECT expressions that are not group function arguments.
Ram Sharma
8,6167 gold badges44 silver badges56 bronze badges
answered Oct 5, 2009 at 15:01
XaisoftXaisoft
45.1k87 gold badges278 silver badges429 bronze badges
0
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
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)
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);
Peter O.
31.7k14 gold badges78 silver badges93 bronze badges
answered Apr 26, 2011 at 18:26
Joseph LustJoseph Lust
18.9k7 gold badges81 silver badges76 bronze badges
3
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 BYclause.
SELECT employee_id, sum(salary)
FROM employees
GROUP BY employee_id;
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 .
SELECT employee_id, sum(salary)
FROM employees;
To correct you need to do one of the following :
- Include all non-aggregated expressions listed in
SELECTclause in the
GROUP BYclause - Remove group (aggregate) function from
SELECTclause.
BobRodes
5,9062 gold badges24 silver badges26 bronze badges
answered Jul 11, 2017 at 6:49
fg78ncfg78nc
4,5883 gold badges18 silver badges30 bronze badges
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;
answered Apr 19, 2016 at 6:28
Same error also come when UPPER or LOWER keyword not used in both place in select expression and 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) .
answered Mar 23, 2017 at 10:52
VijayVijay
4,5561 gold badge28 silver badges37 bronze badges
In addition to the other answers, this error can result if there’s an inconsistency in an order by clause. For instance:
select
substr(year_month, 1, 4)
,count(*) as tot
from
schema.tbl
group by
substr(year_month, 1, 4)
order by
year_month
answered Feb 11, 2019 at 14:57
3pitt3pitt
85912 silver badges21 bronze badges
Adding an alternative solution for @Joseph Lust’s answer regarding random rows in each group. This is made possible in Oracle Database 19c or newer by using any_value keyword. Here is an example:
select customer_id,
max ( order_datetime ),
any_value ( store_id ),
any_value ( order_status ),
any_value ( order_id )
from co.orders
group by customer_id;
answered Oct 18, 2022 at 7:49
Yodi S.Yodi S.
1061 silver badge5 bronze badges
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.
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.deptno from group by clause it will give the same error.
cнŝdk
31k7 gold badges56 silver badges76 bronze badges
answered Apr 7, 2016 at 7:26
Muhammad NadeemMuhammad Nadeem
3604 gold badges7 silver badges21 bronze badges
The answer of «Aaron Digulla» (the first at this time) inspired my solution for the same error code using Spring Boot 2 (JPA / Hibernate) and CriteriaQuery / CriteriaBuilder.
Make a List of selects, and add it to your criteriaQuery.multiselect()
List<Selection> selects = new ArrayList<>();
selects.add(seccionRoot.get("id"));
selects.add(synSeccionRoot.get("DDF"));
selects.add(synSeccionRoot.get("TTYU"));
selects.add(synSeccionRoot.get("4567"));
selects.add(seccionRoot.get("22").get("223"));
selects.add(tasaRoot.get("price"));
selects.add(tasaRoot.get("chair"));
cq.multiselect(selects.toArray(Selection[]::new));
Then you can cast the List to an Expression[]
cq.groupBy(selects.toArray(Expression[]::new));
answered Apr 27, 2022 at 14:46
ieselisraieselisra
3874 silver badges16 bronze badges
I am getting ORA-00979 with the following query:
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.
Ben
51.1k36 gold badges127 silver badges148 bronze badges
asked Oct 5, 2009 at 14:57
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:
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 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?
answered Oct 5, 2009 at 15:02
Aaron DigullaAaron Digulla
317k106 gold badges588 silver badges812 bronze badges
8
Include in the GROUP BY clause all SELECT expressions that are not group function arguments.
Ram Sharma
8,6167 gold badges44 silver badges56 bronze badges
answered Oct 5, 2009 at 15:01
XaisoftXaisoft
45.1k87 gold badges278 silver badges429 bronze badges
0
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
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)
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);
Peter O.
31.7k14 gold badges78 silver badges93 bronze badges
answered Apr 26, 2011 at 18:26
Joseph LustJoseph Lust
18.9k7 gold badges81 silver badges76 bronze badges
3
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 BYclause.
SELECT employee_id, sum(salary)
FROM employees
GROUP BY employee_id;
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 .
SELECT employee_id, sum(salary)
FROM employees;
To correct you need to do one of the following :
- Include all non-aggregated expressions listed in
SELECTclause in the
GROUP BYclause - Remove group (aggregate) function from
SELECTclause.
BobRodes
5,9062 gold badges24 silver badges26 bronze badges
answered Jul 11, 2017 at 6:49
fg78ncfg78nc
4,5883 gold badges18 silver badges30 bronze badges
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;
answered Apr 19, 2016 at 6:28
Same error also come when UPPER or LOWER keyword not used in both place in select expression and 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) .
answered Mar 23, 2017 at 10:52
VijayVijay
4,5561 gold badge28 silver badges37 bronze badges
In addition to the other answers, this error can result if there’s an inconsistency in an order by clause. For instance:
select
substr(year_month, 1, 4)
,count(*) as tot
from
schema.tbl
group by
substr(year_month, 1, 4)
order by
year_month
answered Feb 11, 2019 at 14:57
3pitt3pitt
85912 silver badges21 bronze badges
Adding an alternative solution for @Joseph Lust’s answer regarding random rows in each group. This is made possible in Oracle Database 19c or newer by using any_value keyword. Here is an example:
select customer_id,
max ( order_datetime ),
any_value ( store_id ),
any_value ( order_status ),
any_value ( order_id )
from co.orders
group by customer_id;
answered Oct 18, 2022 at 7:49
Yodi S.Yodi S.
1061 silver badge5 bronze badges
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.
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.deptno from group by clause it will give the same error.
cнŝdk
31k7 gold badges56 silver badges76 bronze badges
answered Apr 7, 2016 at 7:26
Muhammad NadeemMuhammad Nadeem
3604 gold badges7 silver badges21 bronze badges
The answer of «Aaron Digulla» (the first at this time) inspired my solution for the same error code using Spring Boot 2 (JPA / Hibernate) and CriteriaQuery / CriteriaBuilder.
Make a List of selects, and add it to your criteriaQuery.multiselect()
List<Selection> selects = new ArrayList<>();
selects.add(seccionRoot.get("id"));
selects.add(synSeccionRoot.get("DDF"));
selects.add(synSeccionRoot.get("TTYU"));
selects.add(synSeccionRoot.get("4567"));
selects.add(seccionRoot.get("22").get("223"));
selects.add(tasaRoot.get("price"));
selects.add(tasaRoot.get("chair"));
cq.multiselect(selects.toArray(Selection[]::new));
Then you can cast the List to an Expression[]
cq.groupBy(selects.toArray(Expression[]::new));
answered Apr 27, 2022 at 14:46
ieselisraieselisra
3874 silver badges16 bronze badges
Содержание
- How to Fix a ‘Not a GROUP BY Expression’ Error
- Why Does the Oracle Database Report This Error?
- How to Fix the “Not a GROUP BY Expression” Error
- Fix the “Not a GROUP BY Expression” Error and Practice More GROUP BY!
- Sql error 979 42000 ora 00979 выражение не является выражением group by
- The Problem
- The Solution
- Looking Forward
- Oracle GROUP BY дает ошибку ORA-00979: не выражение GROUP BY
- Oracle GROUP BY дает ошибку ORA-00979: не выражение GROUP BY
- 5 ответы
How to Fix a ‘Not a GROUP BY Expression’ Error
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.
| id | name | city | state | last_purchase_date | purchases | |
|---|---|---|---|---|---|---|
| 1 | Peter White | San Francisco | CA | 2020-09-09 | 120.85 | |
| 2 | Helen Brown | San Francisco | CA | 2019-04-11 | 24.85 | |
| 3 | Jeff Grey | Los Angeles | CA | 2020-03-23 | 1085.00 | |
| 4 | Kristine Black | New York | NY | 2020-10-02 | 230.50 | |
| 5 | Donald Page | New York | NY | 2020-06-30 | 2345.00 | |
| 6 | Robert Lee | Los Angeles | CA | 2019-12-06 | 11.00 | |
| 7 | Patrick Collins | San Francisco | CA | 2020-02-12 | 200.30 | |
| 8 | Kate Nord | Buffalo | NY | 2020-05-25 | 435.00 |
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?
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.
| state | city | last_purchase | avg_purchases |
|---|---|---|---|
| CA | San Francisco OR Los Angeles? |
MAX(last_purchase_date) | AVG(purchases) |
| NY | New York OR Buffalo? |
MAX(last_purchase_date) | AVG(purchases) |
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.
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!
You’ve learned why the error “ Not a GROUP BY expression ” occurs and how to fix it. It’s time to put this knowledge into practice with some real-world examples. I recommend starting with these interactive courses on LearnSQL.com:
- 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.
Источник
Sql error 979 42000 ora 00979 выражение не является выражением group by
ORA-00979 is related to the GROUP BY clause. When a user encounters this error, the following message will be displayed:
ORA-00979: not a GROUP BY expression
In comparison to other Oracle errors, ORA-00979 is usually straightforward and can easily be solved by one of three methods.
The Problem
ORA-00979 occurs when the GROUP BY clause does not contain all the expressions in the SELECT clause. Any SELECT expression that is not included in the GROUP function must be listed in the GROUP BY clause. These are AVG, COUNT, MAX, MIN, SUM, STDDEV, and VARIANCE. You may have also tried to execute a SELECT statement that contains a GROUP BY clause.
The Solution
To correct this error, include all of the SELECT expressions in the GROUP BY clause. Make sure the expressions are not group function arguments. There are namely three methods for resolving this error.
- Rewrite the SELECT statement so that the expression or column listed in the SELECT list is also in the GROUP BY clause.
- You may choose to remove the GROUP BY function from the SELECT statement altogether.
- Remove any expressions that do not belong in the GROUP BY clause from the SELECT list.
The first option in fixing the error would apply to the following example in which a user tries to execute a SELECT statement:
SELECT department, class, MAX(number) AS “Highest number of students”
GROUP BY department;
In this example, the error may be corrected by including class in the GROUP BY clause. Class is included in both the SELECT and GROUP BY statements.
SELECT department, class, MAX(number) AS “Highest number of students”
GROUP BY department, class;
Looking Forward
To avoid seeing ORA-00979, make sure that expressions in the SELECT list are also included in the GROUP BY clause. If the expression is also in the GROUP BY clause, you should not see the error. If you continue to see the error and have trouble resolving the issue, contact your database administrator. You may also consider contacting a licensed Oracle consultant. Before using their services, always make sure that they have proper credentials the level of experience needed to handle your Oracle needs.
Источник
Oracle GROUP BY дает ошибку ORA-00979: не выражение GROUP BY
допустим, у нас есть две таблицы
Используя этот запрос
Mysql должен вернуть это
В Oracle я получаю эту ошибку: ORA-00979: not a GROUP BY expression
EDIT: функция MAX в столбце Date не работает, потому что этот столбец является varchar(200) Структура базы данных/таблиц не является моей, и я не могу ее изменить.
Вам нужно сделать одну из двух вещей…
- GROUP BY и использовать агрегированные функции для объединения нескольких записей до одного
- Используйте некоторый поиск, чтобы идентифицировать одну запись, которую вы хотите от группы
В вашем случае вы не просто хотите MAX() из таблицы 1, так как может быть, что более высокий id имеет более низкую date . В этом случае я был бы склонен использовать систему поиска…
ПРИМЕЧАНИЕ. Предполагается, что ваша дата отформатирована так, что буквенно-цифровое упорядочение приведет к правильному порядку даты. Если это не так (и dm-yyyy не будет правильно заказывать), вам необходимо заменить date_field на TO_DATE(date_field) чтобы обеспечить правильный порядок.
ПРИМЕЧАНИЕ. Использование TO_DATE(date_field) также, вероятно, устранит проблемы с MAX().
ПРИМЕЧАНИЕ. Если вы хотите хранить даты в виде строк, но их нужно упорядочить, используйте yyyy-mm-dd
В предложении select и order by инструкции SQL с группой вы можете использовать только столбцы/выражения, используемые в группе, или агрегировать функции (min, max, avg..) других столбцов.
Я знаю это для оракула, и я немного удивлен, что в MySQL это совсем другое.
Группа в oracle подразумевает, что вы пытаетесь запустить агрегированную функцию (сумму, счет и т.д.) На одном из столбцов. Похоже, вы просто хотите заказать по идентификатору сначала, а затем дату. Я бы сделал что-то вроде этого:
Если это не то, что вы намереваетесь, то ответ заключается в том, что вам нужен каждый столбец в предложении select, который не является частью агрегатной функции, которая находится в предложении group.
В ответ на комментарий:
Вероятно, лучше всего сначала отфильтровать таблицу 1 как подзапрос, а затем присоединиться к таблице2:
Вообще говоря, select * from в сочетании с group by – плохая идея. Выражения в предложении select должны быть скалярными, то есть либо совокупными функциями группы, либо не связаны с группировкой, либо самим группой. Select * значительной степени гарантирует, что этого не будет. Вы, вероятно, хотите что-то вроде этого:
Источник
Oracle GROUP BY дает ошибку ORA-00979: не выражение GROUP BY
предположим, у нас есть две таблицы
Используя этот запрос
Mysql должен вернуть это
В Oracle я получаю эту ошибку: ORA-00979: not a GROUP BY expression
РЕДАКТИРОВАТЬ: MAX функция на столбце Date не работает, потому что этот столбец varchar(200) Структура базы данных / таблиц не моя, и я не могу ее изменить.
Разве это не должно быть WHERE table1.ID = table2.ID2 ? — Xavi López
Ага, извини, что забыл написать !! — Marcx
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 ..) других столбцов.
Я знаю это для оракула и немного удивлен, что в MySQL все по-другому.
Источник
Have you tried to run a query and got the “ORA-00979: not a group by expression” error? Learn what it is and how to resolve the error in this article.
The ORA-00979 error happens when you have at least one column in your SELECT clause that is not in your GROUP BY expression when you are using an aggregate function.
Common aggregate functions include SUM, AVG, MIN, MAX, and COUNT. Any column or expression in your SELECT clause must also be listed in the GROUP BY clause.
Here’s an example of a query that will generate the error:
SELECT first_name, last_name, COUNT(*)
FROM student
GROUP BY first_name;
Result:
ORA-00979: not a GROUP BY expression
Why Do I Get The ORA-00979 Error?
This error happens because you’re using an aggregate function, and there is at least one column in the SELECT clause that is not in the GROUP BY clause.
Using the example query above:
SELECT first_name, last_name, COUNT(*)
FROM student
GROUP BY first_name;
Because I use an aggregate function (COUNT), I need to define all of the columns in a GROUP BY clause that are in the SELECT clause.
In this example, I have specified the first_name and last_name columns in the SELECT clause, but the last_name column is not in the GROUP BY clause.
Even if I have some fields in the GROUP BY clause, if I don’t specify all of the fields from the SELECT clause, I’ll still get an error.
For example:
SELECT first_name, last_name, address_state, COUNT(*)
FROM student
GROUP BY first_name, last_nameResult:
ORA-00979: not a GROUP BY expression
As you can see, this will still give me an error.
Why does Oracle give an error?
Because, if you don’t have a GROUP BY but you want to SELECT the column, Oracle doesn’t know what value to show when using this aggregate function. Should it show the first value? The last value? A random value?
How To Resolve the ORA-00979 Error
To resolve the ORA-00979: not a group by expression error, simply ensure that all of the GROUP BY columns match the SELECT clause.
You can do this by adding columns to the GROUP BY.
So, using the example above:
SELECT first_name, last_name, COUNT(*)
FROM student
GROUP BY first_name, last_name;Result:
| FIRST_NAME | LAST_NAME | COUNT(*) |
| John | Smith | 2 |
| Mark | Anderson | 1 |
| Michael | Condor | 1 |
| Brendan | Jefferson | 1 |
| Peter | Stark | 1 |
| Sally | Lincoln | 1 |
| Michelle | Brumby | 1 |
| Amy | Ford | 1 |
| Rose | Minson | 1 |
| Tina | Mitchell | 1 |
Or, using the second example:
SELECT first_name, last_name, address_state, COUNT(*)
FROM student
GROUP BY address_state, first_name, last_nameResult:
| FIRST_NAME | LAST_NAME | ADDRESS_STATE | COUNT(*) |
| John | Smith | California | 1 |
| Mark | Anderson | Texas | 1 |
| Michael | Condor | Florida | 1 |
| Brendan | Jefferson | Florida | 1 |
| Peter | Stark | Illinois | 1 |
| Sally | Lincoln | California | 1 |
| Michelle | Brumby | Texas | 1 |
| Amy | Ford | Texas | 1 |
| Rose | Minson | California | 1 |
| Tina | Mitchell | Illinois | 1 |
| John | Smith | Texas | 1 |
The columns don’t need to be in the same order to correct the error. They just need to be present.
One thing to remember with a GROUP BY clause, is if you give your columns aliases, you need to specify the original column or expression.
So, if you have a query like this:
SELECT first_name + ' ' + last_name AS full_name, COUNT(*)
FROM student
GROUP BY full_name;Result:
ORA-00904: "FULL_NAME": invalid identifier
You get an error because you can’t refer to a column alias within the GROUP BY clause. You’ll need to use a query like this:
SELECT first_name + ' ' + last_name AS full_name, COUNT(*)
FROM student
GROUP BY first_name + ' ' last_name;| FULL_NAME | COUNT(*) |
| John Smith | 2 |
| Mark Anderson | 1 |
| Michael Condor | 1 |
| Brendan Jefferson | 1 |
| Peter Stark | 1 |
| Sally Lincoln | 1 |
| Michelle Brumby | 1 |
| Amy Ford | 1 |
| Rose Minson | 1 |
| Tina Mitchell | 1 |
Conclusion
So, in conclusion, the ORA-00979: not a group by expression error happens because the columns in the SELECT clause don’t match the columns in the GROUP BY clause. To resolve the error, make sure the columns match.
Lastly, if you enjoy the information and career advice I’ve been providing, sign up to my newsletter below to stay up-to-date on my articles. You’ll also receive a fantastic bonus. Thanks!