ORA-00979: not a GROUP BY expression issue

Question

I am performing the following request and I get a "ORA-00979: not a GROUP BY expression" error.

select distinct 
field1, 
field2, 
field3, 
count(*) as field4, 
field5, 
field6,
case
when smt1>0 then 'Deleted'
when smt2>0 then 'Impacted'
when smt3>0 then 'Unknown'
else 'Clean'
end as field7, 
field8, 
field9, 
field10,
field11, 
field12, 
field13
from (<here a big sub query>) A
group by field1, field2
order by field1, field2

I know that I have to put all columns of the SELECT in the GROUP BY statement except the grouping functions ones (like MAX or SUM) so I'm trying the following query but I get the same error message:

select distinct 
field1, 
field2, 
field3, 
count(*) as field4, 
field5, 
field6,
case
when smt1>0 then 'Deleted'
when smt2>0 then 'Impacted'
when smt3>0 then 'Unknown'
else 'Clean'
end as field7, 
field8, 
field9, 
field10,
field11, 
field12, 
field13
from (<here a big sub query>) A
group by field1, field2, field3, field5, field6, field8, field9, field10, field11, field12, field13
order by field1, field2

How can I solve that without changing the overall meaning of the query?

Thank you very much, Martin

Answer 1

you are missing field7 in your group by expression.

Also you cannot use alias in your group by expression of same query. You need to add complete CASE statement in your group by expression to include field7.

Just mentioning an alias is not possible in group by, because the SELECT step is the last step to happen the execution of a query, grouping happens earlier, when alias names are not yet defined.