Oracle FAQ Your Portal to the Oracle Knowledge Grid
HOME | ASK QUESTION | ADD INFO | SEARCH | E-MAIL US
 

Home -> Community -> Usenet -> c.d.o.misc -> Re: Strange behavior in GROUP BY

Re: Strange behavior in GROUP BY

From: <fitzjarrell_at_cox.net>
Date: Thu, 29 Nov 2007 07:40:27 -0800 (PST)
Message-ID: <1ac04d86-e36c-4c38-8253-41547abbbcad@w40g2000hsb.googlegroups.com>


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 - 09:40:27 CST

Original text of this message

HOME | ASK QUESTION | ADD INFO | SEARCH | E-MAIL US