ORA-00937 means a query mixes an aggregate function, such as SUM or COUNT, with a plain column, and has no GROUP BY. An aggregate without GROUP BY returns one row for the whole table, while the plain column has a value per row, so Oracle cannot build the result. Either group by the plain column, drop it, or use an analytic function that keeps every row.
Reproduce the Error
Example:
create table demo_sales (region varchar2(10), amount number);
insert into demo_sales values ('North', 100);
insert into demo_sales values ('North', 250);
insert into demo_sales values ('South', 300);
commit;
select region, sum(amount) from demo_sales;Output:
Table DEMO_SALES created. 1 row inserted. 1 row inserted. 1 row inserted. Commit complete. Error starting at line : 7 In command - select region, sum(amount) from demo_sales Error at Command Line : 7 Column : 8 Error report - SQL Error: ORA-00937: not a single-group group function
SUM(amount) would give one total for all rows, but region has two different values.
Fix: Group, Drop the Column, or Use an Analytic Function
Example:
select region, sum(amount) as total from demo_sales group by region order by region; -- the grand total alone needs no GROUP BY select sum(amount) as total from demo_sales; -- or keep each row and add the total with an analytic function select region, amount, sum(amount) over () as grand_total from demo_sales;
Output:
REGION TOTAL
_________ ________
North 350
South 300
TOTAL
________
650
REGION AMOUNT GRAND_TOTAL
_________ _________ ______________
North 100 650
North 250 650
South 300 650- GROUP BY region gives one total per region.
- Without region, SUM gives the grand total.
- SUM(...) OVER () keeps every row and adds the grand total to each.
Things to Know
- With GROUP BY present but incomplete, the error is ORA-00979, not a GROUP BY expression.
- Constants and other aggregates may appear next to an aggregate without GROUP BY; only plain columns cause the error.
Related Guides
Conclusion
ORA-00937 means an aggregate and a plain column meet in a query without GROUP BY. Add the column to GROUP BY, remove it, or switch to an analytic function such as SUM(...) OVER () when every row must stay in the result.
