Re: Simple? SQL Question

Steven,

> I have a similar table:

domain     varchar2(128)     ->     (like your custno)
initialdate  date                    ->     (like your date)

hmmm, I wonder if you're using an Oracle reserved word (e.g. date) as a column name is causing you a problem? This would be my first guess/place of concern.

numbertimes number -> (like your sales) The following list contains Oracle reserved words. Words followed by an asterisk (*) are also ANSI reserved words.

      ACCESS     ADD     ALL     ALTER     AND*
      ANY*     AS*     ASC*     AUDIT     BETWEEN*
      BY*     CHAR*     CHECK*     CLUSTER     COLUMN
      COMMENT     COMPRESS     CONNECT     CREATE*     CURRENT*
      DATE     DECIMAL     DEFAULT*     DELETE*     DESC*
      DISTINCT*     DROPROW     ELSE     EXCLUSIVE     EXISTS*
      FILE     FLOAT*     FOR*     FROM*     GRANT*
      GROUP*     HAVING*     IDENTIFIED     IMMEDIATE     IN*
      INCREMENT     INDEX     INITIAL     INSERT*     INTEGER*
      INTERSECT     INTO*     IS*     LEVEL     LIKE*
      LOCK     LONG     MAXEXTENTS     MINUS     MODE
      MODIFY     NOAUDIT     NOCOMPRESS     NOT*     NOWAIT
      NULL*     NUMBER     OF*     OFFLINE     ON*
      ONLINE     OPTION*     OR*     ORDER*     PCTFREE
      PRIOR     PRIVILEGES*     PUBLIC*     RAW     RENAME
      RESOURCE     REVOKE     ROWID     ROWLABEL     ROWNUM
      ROWS     SELECT*     SESSION     SET*     SHARE
      SIZE     SMALLINT*     START     SUCCESSFUL     SYNONYM
      SYSDATE     TABLE*     THEN     TO*     TRIGGER
      UID     UNION*     UNIQUE*     UPDATE*     USER*
      VALIDATE     VALUES*     VARCHAR     VARCHAR2     VIEW*
      WHENEVER     WHERE*     WITH*

Anyway back to my similar table and query. The query I used is:

select x.domain, x.sum_total
 from (select domain, sum(numbertimes) sum_total

            from mrp
          where initialdate between to_date('01/01/2002','MM/DD/YYYY') and
to_date('12/31/2002','MM/DD/YYYY')
          group by domain
          order by sum_total desc) x

BTW, I'm on Oracle 9i Release 2 - Windows/XP.

My results are:

DOMAIN                                    SUM_TOTAL
---------------------------------------- ----------
apply.careerbuilder.com                         294
adeccona.com                                    211
teksystems.com                                  174
alliance-consulting.com                         124
princetoninformation.com                        117
yoh.com                                         106
technisource.com                                 98
aol.com                                          87
jobfrenzy.com                                    79
net-temps.com                                    77
spherion.com                                     73
atlantispartners.com                             71
hitecjobs.com                                    71
modisit.com                                      71
remingtoni.com                                   70
sapphire.com                                     69
covansys.com                                     66
kellyservices.com                                55
eurosyst.com                                     47
rcgit.com                                        45
roberthalftechnology.com                         45
comcore.com                                      43
tcml.com                                         43
analysts.com                                     40
cdicorp.com                                      40
bayforce.com                                     39
genp.com                                         39
aquent.com                                       38
comsys.com                                       38
intellimark-it.com                               38
mindspring.com                                   37
1tac.com                                         35
bluware.com                                      35
rcmt.com                                         35
tmp.com                                          35
ctg.com                                          35
hotmail.com                                      34
rhic.com                                         34
agents.icims.com                                 33
i-focuscorp.com                                  33
rydek.com                                        33
cpsinc.com                                       33
ciber.com                                        32
keane.com                                        32
emplifi.com                                      31
iconconsultants.com                              31
apexsystemsinc.com                               30
volt.com                                         30
questent.com                                     30
oaoservices.com                                  29

and as you can see, they are basically what you desire. I noticed the SQL you provide doesn't include a date in the output. Your graphicaly layout shows mutliple dates and I'm not sure what it is suppose to be showing to us. I can think of several possibilities and I'm not going to guess.

Bertram Moshier
Oracle Certified Professional 8i and 9i DBA

http://www.bmoshier.net/bertram

"Steven C. Kondolf" <skondolf_at_garbage.rochester.rr.com> wrote in message news:sf741v0ltobujiuqekg9ieihn14mam3t0v_at_4ax.com...
> 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
Received on Wed Jan 01 2003 - 20:39:38 CET