Re: Using Group By with Expressions

From: Chris Ellis <cellis_at_iol.ie>
Date: 1997/05/23
Message-ID: <33860C40.4022@iol.ie>#1/1

sampsonr_at_spamfool.xyz wrote:
>
> 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

             ^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^

This is not true

> (snip)
>

The GROUP BY clause must contain *all* the expressions in the SELECT list which are not aggregate items.

In this case the query should be:
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 )

Chrysalis. Received on Fri May 23 1997 - 00:00:00 CDT