2

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

OMG Ponies
  • 325,700
  • 82
  • 523
  • 502
MartinMoizard
  • 6,600
  • 12
  • 45
  • 75

2 Answers2

5

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.

Sachin Shanbhag
  • 54,530
  • 11
  • 89
  • 103
  • If I'm adding it, I'm getting the following error: ORA-00904: "field7": invalid identifier – MartinMoizard Feb 17 '11 at 11:23
  • @MartinMoizard - I think you might not be able to add alias in group by of same query. You need to add your complete CASE statement in group by. – Sachin Shanbhag Feb 17 '11 at 11:25
  • @Sachin Shanbhag - Yes it is working, do you know why I don't need to write the 'as field7' part of the CASE statement in my GROUP BY statement? – MartinMoizard Feb 17 '11 at 11:29
  • @MartinMoizard - Explained why you cant use alias in group by in any updated answer. – Sachin Shanbhag Feb 17 '11 at 11:31
  • @Sachin Shanbhag - I am not sure to understand what you mean. Can you explain it in another way? – MartinMoizard Feb 17 '11 at 11:34
  • @MartinMoizard - Lets just say its restriction for Group by and Having clauses. http://dev.mysql.com/doc/refman/5.0/en/group-by-hidden-columns.html – Sachin Shanbhag Feb 17 '11 at 11:52
  • The reason you don't need to add the `as field7` part is because that is an alias, which is only allowed in the SELECT part, not in GROUP BY or HAVING or other parts – GlennFromIowa Nov 20 '15 at 20:48
4

You need to add the expression

case
when smt1>0 then 'Deleted'
when smt2>0 then 'Impacted'
when smt3>0 then 'Unknown'
else 'Clean'
end

into your group by expression.

René Nyffenegger
  • 39,402
  • 33
  • 158
  • 293