ORA-00979 means a query with GROUP BY uses a column, in the select list or ORDER BY, that is neither in the GROUP BY clause nor inside an aggregate function. Each output row stands for a whole group, so a column that can have several values within the group has no single value to show. Oracle AI Database 26ai names the offending column and says what to do with it.
Reproduce the Error
Example:
create table demo_orders (order_id number, customer varchar2(20), region varchar2(10), amount number); insert into demo_orders values (1, 'Ana', 'North', 100); insert into demo_orders values (2, 'Ana', 'North', 50); insert into demo_orders values (3, 'Raj', 'South', 300); commit; select region, customer, sum(amount) from demo_orders group by region; select region, sum(amount) from demo_orders group by region order by customer;
Output:
Table DEMO_ORDERS created. 1 row inserted. 1 row inserted. 1 row inserted. Commit complete. Error starting at line : 7 In command - select region, customer, sum(amount) from demo_orders group by region Error at Command Line : 7 Column : 16 Error report - SQL Error: ORA-00979: "CUSTOMER": must appear in the GROUP BY clause or be used in an aggregate function Error starting at line : 8 In command - select region, sum(amount) from demo_orders group by region order by customer Error at Command Line : 8 Column : 70 Error report - SQL Error: ORA-00979: "CUSTOMER": must appear in the GROUP BY clause or be used in an aggregate function
Both queries group by region only. CUSTOMER appears once in the select list and once in ORDER BY, and either use is enough for the error.
Fix: Group by It, or Aggregate It
Example:
select region, customer, sum(amount) as total from demo_orders group by region, customer order by region, customer; -- a column that should not split the groups can be aggregated instead select region, max(customer) as a_customer, sum(amount) as total from demo_orders group by region order by region;
Output:
REGION CUSTOMER TOTAL _________ ___________ ________ North Ana 150 South Raj 300 REGION A_CUSTOMER TOTAL _________ _____________ ________ North Ana 150 South Raj 300
- Adding customer to GROUP BY gives one row per region and customer.
- When the groups should stay per region, wrap the column in an aggregate, such as MAX, MIN, or LISTAGG.
Things to Know
- Every non-aggregated expression in the select list must appear in GROUP BY, written the same way: GROUP BY UPPER(name) does not cover name.
- Oracle AI Database 26ai accepts select-list aliases in GROUP BY, which keeps long expressions in one place.
- Without any GROUP BY, the same mistake raises ORA-00937 instead.
Related Guides
- How to Fix ORA-00937: Not a Single-Group Group Function in Oracle
- How to Add Subtotals with ROLLUP in Oracle SQL
Conclusion
ORA-00979 names a column that a grouped query uses outside GROUP BY and outside an aggregate. Add it to GROUP BY to split the groups further, or aggregate it to keep the groups as they are.
