# Thread: qrydoubt

1. Registered User
Join Date
Oct 2002
Posts
36

## Unanswered: qrydoubt

I have a small doubt in this qry can u plz help me

SELECT a1,a2,a3,a4,cnt,net FROM (
SELECT a1,a2,a3,a4,ROWNUM as r1,COUNT(*) OVER() AS cnt,sum(a4) over()as net FROM(
p.dname a2,
c.deptno a3,
c.sal a4
FROM emp c,
dept p
WHERE p.deptno = c.deptno
AND c.deptno = 30
ORDER BY c.hiredate DESC
)
)
WHERE r1 BETWEEN 1 AND 4

cls A1 A2 A3 A4 CNT NET
---------- -------------- ---------- ---------- ---------- ----------
7900 SALES 30 950 6 9400
7654 SALES 30 1250 6 9400
7844 SALES 30 1500 6 9400
7698 SALES 30 2850 6 9400

here from the above qry iam getting x and y range rows,count and total for the complete set of rows.
that with deptno 30 total 6 records are there iam getting count and total of that.but what ineed along with the
above information i want total of specified range of records that is total of 1 to 4 records which comes
950+1250+1500+2850=6550.total 6550 should also come how can we get the same

Thank U.

2. Moderator.
Join Date
Sep 2002
Location
UK
Posts
5,171
Provided Answers: 1

## Re: qrydoubt

Couldn't quote follow your "qry" (where did a1 come from?)

But anyway, I think you can get the answer you want by doing something like this:

SELECT dname,deptno,sal,COUNT(*) OVER() AS cnt,sum(sal) over() as net
FROM
(
SELECT dname,deptno,sal FROM
(
SELECT
p.dname,
c.deptno,
c.sal
FROM emp c,
dept p
WHERE p.deptno = c.deptno
AND c.deptno = 30
ORDER BY c.hiredate DESC
)
WHERE ROWNUM BETWEEN 1 AND 4
)

I found your aliases a1..a4 confusing, which is why you won't see them in my query.

3. Moderator.
Join Date
Sep 2002
Location
UK
Posts
5,171
Provided Answers: 1

## Re: qrydoubt

I just realised, A1 was obviously EMPNO! And I have just run my query against the same tables:

1 SELECT empno, dname,deptno,sal,COUNT(*) OVER() AS cnt,sum(sal) over() as net
2 FROM
3 (
4 SELECT empno, dname,deptno,sal FROM
5 (
6 SELECT
7 c.empno,
8 p.dname,
9 c.deptno,
10 c.sal
11 FROM emp c,
12 dept p
13 WHERE p.deptno = c.deptno
14 AND c.deptno = 30
15 ORDER BY c.hiredate DESC
16 )
17 WHERE ROWNUM BETWEEN 1 AND 4
18* )

EMPNO DNAME DEPTNO SAL CNT NET
---------- -------------- ---------- ---------- ---------- ----------
7900 SALES 30 950 4 6550
7654 SALES 30 1250 4 6550
7844 SALES 30 1500 4 6550
7698 SALES 30 2850 4 6550

#### Posting Permissions

• You may not post new threads
• You may not post replies
• You may not post attachments
• You may not edit your posts
•