Ora-00979: Not A Group By Expression When I Execute My Sql

When I execute my SQL, I get the below error: My exception is: [SQL]select bd.name aaa from bd_material_v m ,bd_marbasclass bd where m.creator='1001A210000000000IIQ' and bd.

Solution 1:

The issue is that you are ordering by something that is not in your group by clause.

For example, this works

SQL>with testGroup as ( select1asone, 2as two from dual)
  2selectone3from testGroup
  4groupbyone;

       ONE----------1

If you order by a column that is not in your group by clause:

SQL> with testGroup as ( select1as one, 2as two from dual)
  2select one
  3from testGroup
  4groupby two;
select one
       *
ERROR at line 2:
ORA-00979: not a GROUPBY expression

If you edit the group by clause to handle the column you need in the order by:

SQL>with testGroup as ( select1asone, 2as two from dual)
  2selectone3from testGroup
  4groupbyone, two;

       ONE----------1SQL>

Solution 2:

Use select distinct instead:

selectdistinct bd.name aaa , 
       substr(m.code,0,4) -- see below code comment, either include this or remove the orderbyfrom bd_material_v m
inner join bd_marbasclass bd
      on bd.code=substr(m.code,0,4)  
where m.creator='1001A210000000000IIQ'orderby substr(m.code,0,4) -- As this is oracle, you can only orderby selected columns/expressions :D

Solution 3:

Even though your join syntax is unconventional, the problem in your code is order by. Remove it and you will not get the not a group by error.

But I strongly suggest to correct the join syntax. use inner join on appropriate column.

select bd.name aaa 
  from bd_material_v m ,bd_marbasclass bd
    where m.creator='1001A210000000000IIQ'and bd.code=substr(m.code,0,4)  
      groupby bd.name 

So this will not give you error

with tbl(col1,col2) as 
(select1,1from dual union all
select1,3from dual )
select col1 as col1 
from tbl 
groupby col1

But this will

with tbl(col1,col2) as 
(select1,1from dual union all
select1,3from dual )
select col1 as col1 
from tbl 
groupby col1
orderby col2