2

This is my code:

BREAK ON DEPTNO SKIP 1
compute sum of sal on deptno
SELECT  deptno, empno, ename,sal FROM
(SELECT  deptno, empno, ename, sal FROM emp group by deptno, empno, ename, sal  order by DEPTNO)
  WHERE   ROWNUM <= 2;

But the result is:

    DEPTNO      EMPNO ENAME             SAL
---------- ---------- ---------- ----------
        10       7782 CLARK            2450
                 7839 KING             5000
**********                       ----------
sum                                    7450

What is good, but I want to get also on deptno 20, deptno 30: (This is expected result, all in the same return - for deptno 10, 20,30)

    DEPTNO      EMPNO ENAME             SAL
---------- ---------- ---------- ----------
        10       7782 CLARK            2450
                 7839 KING             5000
**********                       ----------
sum                                    7450

    DEPTNO      EMPNO ENAME             SAL
---------- ---------- ---------- ----------
        20       7788 SCOTT            3000
                 7902 FORD             3000
                 7566 JONES            2975
**********                       ----------
sum                                    8975

    DEPTNO      EMPNO ENAME                SAL
---------- ---------- ---------- ----------
        30       7698 BLAKE            2850
                 7499 ALLEN            1600
**********                       ----------
sum                                    4450

My question is how to sum two highest salary on table EMP on deptno (deptno 10,20,30) using BREAK and COMPUTE SUM all in one return (just like expected above)?

I think my code is closely good, but is missing something.

Thorsten Kettner
  • 89,309
  • 7
  • 49
  • 73
Goran Bart Bartolić
  • 81
  • 9

1 Answers1

0

Sorry to say, your query doesn't make much sense. You select from emp and group by empno (and other columns), so you say: "Give me data from emp, but make it one record per empno", which means no grouping at all (as there is exactly one record per empno in emp).

Your subquery

SELECT deptno, empno, ename, sal 
FROM emp
group by deptno, empno, ename, sal 
order by DEPTNO

is the same as

SELECT deptno, empno, ename, sal 
FROM emp
order by DEPTNO

Then you take the two first rows after having the records ordered by deptno, so you get two emp records arbitrarily chosen for the first deptno.

What you want instead is show emp records (i.e. no group by) with the two highest salaries per dept. You can rank salaries per departmnent with the analytic function DENSE_RANK. Then stay with the records ranked 1 and 2.

select deptno, empno, ename, sal
from
(
  select 
    deptno, empno, ename, sal,
    dense_rank() over (partition by deptno order by sal desc) as rnk
  from emp 
)
where rnk <= 2
order by deptno, sal desc;
Thorsten Kettner
  • 89,309
  • 7
  • 49
  • 73
  • Thank you a lot, you are absolutely right. Now I can get two highest salary per deptno, but I can't sum that two highest salary in the way I want. If I put " compute sum of sal on deptno" , it will sum every record individually, for example : 10 7839 KING 5000 , SUM 5000 ... 7782 CLARK 2450 , SUM 2450 .. I want common amount that two numbers, 5000+2450 that is 7450... Is that possible? thank you for your time – Goran Bart Bartolić Feb 04 '16 at 11:07
  • I am sorry, I don't know the SQL*Plus report functions. I assumed, too, that `compute sum of sal on deptno` computes the total salary per deptno, not per empno. I don't know why this is not the case and how to get that result. And strange that it worked correctly with your original query and with the new one it doesn't. – Thorsten Kettner Feb 04 '16 at 11:22
  • The docs in https://docs.oracle.com/cd/B19306_01/server.102/b14357/ch12014.htm suggest that we assume correctly (see example with `COMPUTE SUM LABEL 'TOTAL' OF SALARY ON JOB_ID`. So it seems either your SQL*Plus version is flawed or you have a typo somewhere. – Thorsten Kettner Feb 04 '16 at 11:33
  • Never mind, i will find the solution! Thank you one more time Thorsten, you helped me a lot :) – Goran Bart Bartolić Feb 04 '16 at 11:34