Re: Simple? SQL Question

[Quoted] Thanks for the reply but I'm still getting a ORA-00907 missing right parenthesis at line 6.

On Wed, 01 Jan 2003 00:37:55 GMT, Karsten Farrell <kfarrell_at_belgariad.com> wrote:

>Steven C. Kondolf wrote:
>> We are developing a report w/reports 2.5 and Oracle 8.0.5 and need
>> determine the "top 50" customers based total yearly sales using the
>> following table layout:
>>
>> custno Date Sales
>>
>> 1 200201 500
>> 1 200201 1000
>> 1 200202 600
>> 2 200201 700
>> 2 200202 900
>> .
>> .
>> .
>>
>> We can do a simple select with a group by clause such as:
>>
>> select custno, sum(sales)
>> from table
>> where date between 200201 and 200212
>> order by sum(sales) desc
>> group by custno;
>>
>> And get a list of ALL customers sorted in descending sales$ order but
>> we're having trouble generating the proper data for the "top 50"
>> portion.
>>
>> I know I can create a view summarizing the data and then do a simple
>> select using rownum<51 but we really don't want to create a temp table
>> or view for this purpose if we can get around it.
>>
>> I've seen suggestions using a sub-select as follows:
>>
>> select custno, sum(sales)
>> from (select custno, sum(sales)
>> from table
>> where date between 200201 and 200212
>> group by custno
>> order by sum(sales) desc)
>> where rownum<51;
>>
>> But I get an error about a "missing right parenthesis" on the order by
>> line in the sub-select
>>
>> Any suggestions how this can be accomplished.
>> Peace,
>> Steve
>> skondolf_at_garbage.rochester.rr.com
>You have to give the inline view an alias name. You also need to give
>the sum an alias. Try something like this:
>
>select x.custno, x.sum_sales
>from (select custno, sum(sales) as sum_sales
> from table
> where date between 200201 and 200212
> group by custno
> order by sum(sales) desc) x
>where rownum<51;

Peace,
Steve
skondolf_at_garbage.rochester.rr.com Received on Wed Jan 01 2003 - 18:55:43 CET