Re: Using Group By with Expressions

On 23 May 1997 00:35:21 GMT, patrick_at_pcgi.com wrote:

>I'm using Oracle 7.2. I have a SQL Statement that looks roughly
>like this:
>
>SELECT TBL.COL1,
> TBL.COL2,
> DECODE ( TBL.COL3, "Value", TBL.COL3, TBL.COL4 ),
> SUM ( TBL.COL5 )
>FROM TBL
>GROUP BY COL1,
> COL2,
> ????
>
>Of course the ??? don't work. What I need to know is, how do I
>reference the computed column in my Group By? If I don't include
>it I get an error, and besides, it is legitimately part of the
>grouping. What I've tried:

A couple of options:

1. Use a correlated sub-query to calculate the sum for you.
2. The error you probably got was "Not a group-by expression". Oracle apparently only wants to see one other expression in the SELECT other than the group function(s). The GROUP BY should also exactly replicate the expression in the SELECT. Try this instead:

>SELECT TBL.COL1||' '||TBL.COL2||' '||DECODE( TBL.COL3, "Value", TBL.COL3, TBL.COL4 ),
> SUM ( TBL.COL5 )
>FROM TBL
>GROUP BY TBL.COL1||' '||TBL.COL2||' '||DECODE( TBL.COL3, "Value", TBL.COL3, TBL.COL4 )

Robert Sampson
sampsonr_at_ncr.disa.mil Received on Fri May 23 1997 - 00:00:00 CDT