Tek-Tips is the largest IT community on the Internet today!

Members share and learn making Tek-Tips Forums the best source of peer-reviewed technical information on the Internet!

  • Congratulations Mike Lewis on being selected by the Tek-Tips community for having the most helpful posts in the forums last week. Way to Go!

Group by challenge 1

Status
Not open for further replies.

mibeach7

MIS
Jun 18, 2003
35
US

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!

 
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
 
[thumbsup2]
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 [wiggle]

---- Andy

"Hmm...they have the internet on computers now"--Homer Simpson
 
I believe Andy is correct. You must use that actual field name or the position number of the field in the GROUP BY.
 
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
 
Status
Not open for further replies.

Part and Inventory Search

Sponsor

Back
Top