Group by challenge 1

[mibeach7]

greetings

I have this sql and want to count/group by.
SQL> select SUBSTR(msg, INSTR(msg, 'ORA-', -4),9) as ERR FROM sysman.mgmt$events where MSG like '%ORA-%' and rownum < 6;

ERR
------------------------------------
ORA-12505
ORA-12505
ORA-12505
ORA-28000
ORA-28000

Group by fails
select count(*), SUBSTR(msg, INSTR(msg, 'CRS-', -4),8) as ERR FROM sysman.mgmt$events where MSG like '%CRS-%' and rownum < 6 group by ERR;
ERROR at line 1:
ORA-00904: "ERR": invalid identifier

any tips is appreciated thanks!

 

[Andrzejek]

ERR is the name of your alias.

Try:[tt]
select count(*)[red] As MyCount[/red],
[blue]SUBSTR(msg, INSTR(msg, 'CRS-', -4),8) [/blue]as ERR
FROM sysman.mgmt$events
where MSG like '%CRS-%'
and rownum < 6
group by [blue]SUBSTR(msg, INSTR(msg, 'CRS-', -4),8) [/blue][/tt]

---- Andy

"Hmm...they have the internet on computers now"--Homer Simpson

 

[mibeach7]

yes that worked perfect!
Big Thanks!

 

[Andrzejek]

Just to clarify my statement:
ERR is just the name of your alias and (I think...) cannot be used in ORDER BY portion of the Select statement. You need to use the actual field in GROUP BY
I am glad it works for you

---- Andy

"Hmm...they have the internet on computers now"--Homer Simpson

 

[johnherman]

I believe Andy is correct. You must use that actual field name or the position number of the field in the GROUP BY.

 

[Andrzejek]

Oh yeah, the position number!
So the Select statement could be simple:
[tt]
select count(*) As MyCount,
SUBSTR(msg, INSTR(msg, 'CRS-', -4),8) as ERR
FROM sysman.mgmt$events
where MSG like '%CRS-%'
and rownum < 6
group by [highlight #FCE94F]2[/highlight]
[/tt]

---- Andy

"Hmm...they have the internet on computers now"--Homer Simpson