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!

How to take total count using group by./!

Discussion in 'SQL PL/SQL' started by Vicky, Mar 25, 2015.

  1. Vicky

    Vicky Forum Advisor

    Messages:
    261
    Likes Received:
    11
    Trophy Points:
    260
    Location:
    Chennai
    Hii..

    I'm using the below qry :

    select COUNT(1) as CNT,TOTAL_INSTR as instr from EMPLOYEES group by TOTAL_INSTR ;
    union
    select SUM(COUNT(1)) as CNT,SUM(TOTAL_INSTR) as INSTR from EMPLOYEES group by TOTAL_INSTR;

    And the result I can get is

    CNT INSTR
    --- -----
    1 3
    2 2
    2 4
    2 5
    6 1
    13 15

    6 rows selected ..

    is it possible the achieve the result in the single qry itself.,/?
     
  2. eras

    eras Active Member

    Messages:
    23
    Likes Received:
    9
    Trophy Points:
    90
    Location:
    Lithuania
    Code (SQL):

    SELECT COUNT(1) cnt,
             CASE
                WHEN grouping_id(total_instr) = 1 THEN
                   SUM(DISTINCT total_instr)
             ELSE
                total_instr
             END instr  
    FROM employees
    GROUP BY rollup(total_instr)
     
     
    Vicky likes this.