Results 1 to 3 of 3

Thread: qrydoubt

  1. #1
    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. #2
    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. #3
    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
  •