Re: Re Crack this Query

From: Tom Verhagen <tverhage_at_aon.nl>
Date: Mon, 11 Oct 1999 14:10:41 +0200
Message-ID: <3801D3C1.AE0EB1D_at_aon.nl>


ok change the fileds to the fields you mentioned, and add a UNION for all those employees theres no department listed.

Kannan Anantharaman wrote:

> sorry my friend, your solution does not seem to work. for your convenience,
> i am hereunder listing out the description of the EMP table :-
>
> Name Null? Type
> ------------------------------- -------- ----
> EMPNO NOT NULL NUMBER(4)
> ENAME VARCHAR2(10)
> JOB VARCHAR2(9)
> MGR NUMBER(4)
> HIREDATE DATE
> SAL NUMBER(7,2)
> COMM NUMBER(7,2)
> DEPTNO NUMBER(2)
>
> thanks for trying and keep trying ....
>
> KaNnAn
>
> *** On the Keyboard of Life, ***
> *** Always keep one hand ***
> *** on the Escape Key !! ***
>
> ----- Original Message -----
> From: Tom Verhagen <tverhage_at_aon.nl>
> Newsgroups: comp.databases.oracle.tools
> Sent: Monday, October 11, 1999 03:06
> Subject: Re: crack this SQL query
>
> > maybe this will do the trick
> >
> > 1 select a.deptno,a.emp,a.sal,sum(b.sal)
> > 2 from emp a
> > 3 ,emp b
> > 4 where a.deptno=b.deptno
> > 5 and b.emp<= a.emp
> > 6* group by a.deptno,a.emp,a.sal
> > hpg40c.DIB1 SQL> /
> >
> > DEPTNO EMP SAL SUM(B.SAL)
> > ======== === ======== ==========
> > 10 a 100 100
> > 10 b 125 225
> > 10 c 127 352
> > 30 de 260 260
> > 30 fg 2030 2290
> >
> >
> > Kannan Anantharaman wrote:
> >
> > > hii there. i have a very unique SQL query, which i have been posting to
> many
> > > oracle pros. but i can't seem to get the right answer. so if anyone can
> > > crack this, i'll be very grateful.
> > >
> > > the query goes like this .... GIVE THE SELECT STATEMENT TO LIST THE
> EMPLOYEE
> > > NAMES AND THEIR CUMULATIVE SALARIES, DEPARTMENT WISE. you have to use
> the
> > > EMP table which comes along with the demo database.
> > >
> > > in short, the query should produce the results in the following format
> :-
> > > (approx.)
> > >
> > > deptno ename sal cumsal
> > > 10 abcd 1000 1000
> > > efgh 2000 3000
> > > ijkl 1500 4500
> > > 20 xyz 4000 4000
> > > pqr 2400 6400
> > >
> > > please help me solve this long-standing sleepless-nights-giving query.
> >
>
> --
> KaNnAn
>
> *** On the Keyboard of Life, ***
> *** Always keep one hand ***
> *** on the Escape Key !! ***
Received on Mon Oct 11 1999 - 14:10:41 CEST

Original text of this message