Re: Strange behavior in GROUP BY

Thanks David. I can't believe I messed up the paste so bad...

Kostis

On Nov 29, 5:40 pm, "fitzjarr..._at_cox.net" <fitzjarr..._at_cox.net> wrote:
> On Nov 29, 9:03 am, vezerid <veze..._at_act.edu> wrote:
>
>
>
> > Hi all,
>
> > the following query attempts to produce the total salary per the
> > number of 'A's in employee name (scott/tiger) and it works.
>
> > select length(ename)-length(replace(ename,'A','')), sum(sal)
> > from emp
> > group by length(ename)-length(replace(ename,'A',''));
>
> > The next query tries to beautify output and it does not:
>
> > select 'number of A is ' || length(ename)-
> > length(replace(ename,'A','')), sum(sal)
> > from emp
> > group by 'number of A is ' || length(ename)-
> > length(replace(ename,'A',''));
>
> > I get an unexpected (for me) error, which I cannot figure out:
>
> > group by 'number of A is ' || length(ename)-
> > length(replace(ename,'A',''))
> > *
> > ERROR at line 3:
> > ORA-01722: invalid number
>
> > (asterisk appears below the second bar of concat operator ||)
>
> > Something wrong with concatenation in GROUP BY? Something else?
>
> > TIA
> > Kostis Vezerides
>
> It's your 'formula' that's suspect. It should be:
>
> select 'number of A is ' || (length(ename) -
> length(replace(ename,'A',''))), sum(sal)
> from emp
> group by 'number of A is ' || (length(ename) -
> length(replace(ename,'A','')));
>
> 'NUMBEROFAIS'||(LENGTH(ENAME)-LENGTH(REPLACE(ENAME,'A', SUM(SAL)
> ------------------------------------------------------- ----------
> number of A is 1 10350
> number of A is 0 17575
> number of A is 2 1100
>
> David Fitzjarrell
Received on Thu Nov 29 2007 - 10:24:04 CST