1. Get rid of all advertisements and get unlimited access to documents by upgrading to Premium Membership. Upgrade to Premium Now and also get a Premium Badge!

To find maximum of salary for deptno wise and whole table

Discussion in 'SQL PL/SQL' started by Bharat, Jun 1, 2012.

  1. Bharat

    Bharat Community Moderator Forum Guru

    Messages:
    1,747
    Likes Received:
    147
    Trophy Points:
    1,805
    Location:
    Vijayawada, India
    Hi all,

    Problem: - How to find max of sal for deptno wise and whole table.

    Solution: - We can use Rollup concept to do this. This explains as shown in example below:

    Rollup: - The ROLLUP operation in the simple_grouping_clause groups the selected rows based on the values of the first n, n-1, n-2, ... 0 expressions in the GROUP BY specification, and returns a single row of summary for each group. You can use the ROLLUP operation to produce subtotal values by using it with the SUM function. When used with SUM, ROLLUP generates subtotals from the most detailed level to the grand total.

    Code (Text):


    Query: -

    select deptno,ename,max(sal) from emp group by rollup(deptno,ename);

    Output gives maximum salary deptno wise and ename wise and also gives overall maximum salary.

    Output: -

    DEPTNO  ENAME   MAX(SAL)
    10  KING    5000
    10  CLARK   2450
    10  MILLER  1300
    10      5000
    20  BPO 100
    20  FORD    3000
    20  ADAMS   1100
    20  JONES   2975
    20  SCOTT   3000
    20  SMITH   800
    20      3000
    30  WARD    1250
    30  ALLEN  
    30  BLAKE   2850
    30  JAMES   950
    30  MARTIN  1250
    30      2850
            5000


     
     
  2. kiran.marla

    kiran.marla Forum Genius

    Messages:
    403
    Likes Received:
    52
    Trophy Points:
    505
    Location:
    Khammam
    extension to Bharat,

    cube is an operator which gives job wise and dept,job wise total

    Code (SQL):
    SQL> SELECT deptno,job,MAX(sal) FROM scott.emp GROUP BY cube(deptno,job);

        DEPTNO JOB         MAX(SAL)
    ---------- --------- ----------
                               5000
               CLERK           1300
               ANALYST         3000
               MANAGER         2975
               SALESMAN        1600
               PRESIDENT       5000
            10                 5000
            10 CLERK           1300
            10 MANAGER         2450
            10 PRESIDENT       5000
            20                 3000
            20 CLERK           1100
            20 ANALYST         3000
            20 MANAGER         2975
            30                 2850
            30 CLERK            950
            30 MANAGER         2850
            30 SALESMAN        1600

    18 ROWS selected.

    SQL>
     
    Bharat likes this.
  3. zargon

    zargon Community Moderator Forum Guru

    Messages:
    2,351
    Likes Received:
    348
    Trophy Points:
    1,430
    Location:
    Aurora, CO
    There are a number of ways to return such a result; two have been posted here and neither is better or worse than the other -- it depends on what is to be done with the numbers after the results are generated. To me the rollup method produces a more 'readable' report but the cube method does produce usable results.

    It's nice to see people exploring the database and showing others how it works.