Re: sql query formatting

This is the best I could do:

First create a view using the group by rollup clause:

create view test as (

    select dname,job,count(*) as "Total Empl.",     avg(sal) as "Avg. Sal."
    from emp, dept
    where dept.deptno = emp.deptno
    group by rollup (dname,job)
    );

Then select from that view to get some of your desired formatting done:

  1 select decode(job,null,null,dname) as Department,

  2         nvl(job,'All Jobs') as Job,"Total Empl.",
  3*        "Avg. Sal." from test

DEPARTMENT     JOB       Total Empl.  Avg. Sal.
-------------- --------- ----------- ----------
ACCOUNTING     CLERK               1  $1,300.00
ACCOUNTING     MANAGER             1  $2,450.00
ACCOUNTING     PRESIDENT           1  $5,000.00
               All Jobs            3  $2,916.67
RESEARCH       ANALYST             2  $3,000.00
RESEARCH       CLERK               2    $950.00
RESEARCH       MANAGER             1  $2,975.00
               All Jobs            5  $2,175.00
SALES          CLERK               1    $950.00
SALES          MANAGER             1  $2,850.00
SALES          SALESMAN            4  $1,400.00
               All Jobs            6  $1,566.67
               All Jobs           14  $2,073.21

Hope this helps!

Wayne

DWLMB <bruzek_at_erols.com> wrote in message news:9lcdg2$a22$1_at_bob.news.rcn.net...
> mayhaps a group by rollup(blah, blah, blah) clause?
>
>
> Madis Priilinn <madp_at_math.ut.ee> wrote in message
> news:Pine.GSO.4.33.0108141634540.29500-100000_at_madli.ut.ee...
> >
> > Hello!
> >
> > I would like to format one query. Right now it shows something like
this
> > example (grouped by first column):
> >
> >
> > DNAME JOB Total Empl Average Sa
> > --------------- --------- ---------- ----------
> > ACCOUNTING CLERK 1 15600
> > ACCOUNTING MANAGER 1 29400
> > ACCOUNTING PRESIDENT 1 60000
> > ACCOUNTING All Jobs 3 35000
> > RESEARCH ANALYST 2 36000
> > RESEARCH CLERK 2 11400
> > RESEARCH MANAGER 1 35700
> > RESEARCH All Jobs 5 26100
> >
> > But my intention is to get something like this:
> >
> > DNAME JOB Total Empl Average Sa
> > --------------- --------- ---------- ----------
> > ACCOUNTING CLERK 1 15600
> > MANAGER 1 29400
> > PRESIDENT 1 60000
> > All Jobs 3 35000
> > RESEARCH ANALYST 2 36000
> > CLERK 2 11400
> > MANAGER 1 35700
> > All Jobs 5 26100
> >
> >
> > I've searched several hours already but can't find anything...But I
don't
> > want to believe this is impossible to do :)
> >
> > Thank You,
> >
> > Madis
> >
>
>
Received on Tue Aug 14 2001 - 19:33:33 CDT