Re: Simple? SQL Question

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