Forum Stats

  • 3,875,427 Users
  • 2,266,915 Discussions
  • 7,912,205 Comments

Discussions

Pivot - Grand total

Rengudi
Rengudi Member Posts: 587 Bronze Badge

Hi

how to get display grand total like below pivot

CREATE table input_table(First_Name,Last_Name,Country_Region,State) AS

SELECT 'Cameron' AS First_Name,'Greene' AS Last_Name,'India - East' AS Country_Region,'bihar' AS State FROM dual

UNION ALL

SELECT 'Dorothy','Clarkson','India - South','puducherry' FROM dual

UNION ALL

SELECT 'Kevin','Cornish','India - East','bihar' FROM dual

UNION ALL

SELECT 'Neil','Springer','India - West','gujarat' FROM dual

UNION ALL

SELECT 'Alison','Nash','India - West','gujarat' FROM dual

UNION ALL

SELECT 'Ian','Sutherland','India - North','delhi' FROM dual

UNION ALL

SELECT 'Diana','Thomson','India - East','bihar' FROM dual

UNION ALL

SELECT 'Gabrielle','Ross','India - South','puducherry' FROM dual

UNION ALL

SELECT 'Victor','Wright','India - East','bihar' FROM dual

UNION ALL

SELECT 'Neil','Young','India - West','gujarat' FROM dual

UNION ALL

SELECT 'Alexandra','Marshall','India - West','gujarat' FROM dual

UNION ALL

SELECT 'John','McLean','India - North','delhi' FROM dual

UNION ALL

SELECT 'Victor','Nash','India - East','bihar' FROM dual

UNION ALL

SELECT 'Alan','Piper','India - South','puducherry' FROM dual

UNION ALL

SELECT 'Amelia','Ross','India - East','bihar' FROM dual

UNION ALL

SELECT 'Ian','Pullman','India - West','gujarat' FROM dual

UNION ALL

SELECT 'Brandon','Berry','India - West','gujarat' FROM dual

UNION ALL

SELECT 'James','MacDonald','India - North','delhi' FROM dual

UNION ALL

SELECT 'Nicholas','Wallace','India - East','bihar' FROM dual

UNION ALL

SELECT 'Carolyn','Terry','India - South','puducherry' FROM dual

UNION ALL

SELECT 'Joan','Mathis','India - East','bihar' FROM dual

UNION ALL

SELECT 'Gavin','Peake','India - West','gujarat' FROM dual

UNION ALL

SELECT 'Victoria','Ince','India - West','gujarat' FROM dual

UNION ALL

SELECT 'Christopher','Underwood','India - North','delhi' FROM dual

UNION ALL

SELECT 'Stephanie','Oliver','India - North','delhi' FROM dual

UNION ALL

SELECT 'Tracey','James','India - East','bihar' FROM dual

UNION ALL

SELECT 'Sophie','Mackenzie','India - South','puducherry' FROM dual

UNION ALL

SELECT 'James','Knox','India - East','bihar' FROM dual

UNION ALL

SELECT 'Colin','Harris','India - West','gujarat' FROM dual

UNION ALL

SELECT 'Nathan','McLean','India - West','gujarat' FROM dual

UNION ALL

SELECT 'Nathan','Morrison','India - North','delhi' FROM dual

UNION ALL

SELECT 'Jack','North','India - East','bihar' FROM dual

UNION ALL

SELECT 'Anna','Walker','India - South','puducherry' FROM dual

UNION ALL

SELECT 'Natalie','Harris','India - East','bihar' FROM dual

UNION ALL

SELECT 'Piers','Greene','India - West','gujarat' FROM dual

UNION ALL

SELECT 'Bella','Butler','India - West','gujarat' FROM dual

UNION ALL

SELECT 'Faith','Reid','India - North','delhi' FROM dual

UNION ALL

SELECT 'Jacob','Parsons','India - East','bihar' FROM dual

UNION ALL

SELECT 'Alan','Bailey','India - South','puducherry' FROM dual

UNION ALL

SELECT 'Katherine','Ferguson','India - East','bihar' FROM dual;


expected output


Thanks!

Tagged:

Best Answer

  • Stax
    Stax Member Posts: 184 Silver Badge
    Answer ✓

    😉

    with t(First_Name,Last_Name,Country_Region,State) AS (
    SELECT 'Cameron' AS First_Name,'Greene' AS Last_Name,'India - East' AS Country_Region,'bihar' AS State FROM dual
    UNION ALL
    SELECT 'Dorothy','Clarkson','India - South','puducherry' FROM dual
    UNION ALL
    SELECT 'Kevin','Cornish','India - East','bihar' FROM dual
    UNION ALL
    SELECT 'Neil','Springer','India - West','gujarat' FROM dual
    UNION ALL
    SELECT 'Alison','Nash','India - West','gujarat' FROM dual
    UNION ALL
    SELECT 'Ian','Sutherland','India - North','delhi' FROM dual
    UNION ALL
    SELECT 'Diana','Thomson','India - East','bihar' FROM dual
    UNION ALL
    SELECT 'Gabrielle','Ross','India - South','puducherry' FROM dual
    UNION ALL
    SELECT 'Victor','Wright','India - East','bihar' FROM dual
    UNION ALL
    SELECT 'Neil','Young','India - West','gujarat' FROM dual
    UNION ALL
    SELECT 'Alexandra','Marshall','India - West','gujarat' FROM dual
    UNION ALL
    SELECT 'John','McLean','India - North','delhi' FROM dual
    UNION ALL
    SELECT 'Victor','Nash','India - East','bihar' FROM dual
    UNION ALL
    SELECT 'Alan','Piper','India - South','puducherry' FROM dual
    UNION ALL
    SELECT 'Amelia','Ross','India - East','bihar' FROM dual
    UNION ALL
    SELECT 'Ian','Pullman','India - West','gujarat' FROM dual
    UNION ALL
    SELECT 'Brandon','Berry','India - West','gujarat' FROM dual
    UNION ALL
    SELECT 'James','MacDonald','India - North','delhi' FROM dual
    UNION ALL
    SELECT 'Nicholas','Wallace','India - East','bihar' FROM dual
    UNION ALL
    SELECT 'Carolyn','Terry','India - South','puducherry' FROM dual
    UNION ALL
    SELECT 'Joan','Mathis','India - East','bihar' FROM dual
    UNION ALL
    SELECT 'Gavin','Peake','India - West','gujarat' FROM dual
    UNION ALL
    SELECT 'Victoria','Ince','India - West','gujarat' FROM dual
    UNION ALL
    SELECT 'Christopher','Underwood','India - North','delhi' FROM dual
    UNION ALL
    SELECT 'Stephanie','Oliver','India - North','delhi' FROM dual
    UNION ALL
    SELECT 'Tracey','James','India - East','bihar' FROM dual
    UNION ALL
    SELECT 'Sophie','Mackenzie','India - South','puducherry' FROM dual
    UNION ALL
    SELECT 'James','Knox','India - East','bihar' FROM dual
    UNION ALL
    SELECT 'Colin','Harris','India - West','gujarat' FROM dual
    UNION ALL
    SELECT 'Nathan','McLean','India - West','gujarat' FROM dual
    UNION ALL
    SELECT 'Nathan','Morrison','India - North','delhi' FROM dual
    UNION ALL
    SELECT 'Jack','North','India - East','bihar' FROM dual
    UNION ALL
    SELECT 'Anna','Walker','India - South','puducherry' FROM dual
    UNION ALL
    SELECT 'Natalie','Harris','India - East','bihar' FROM dual
    UNION ALL
    SELECT 'Piers','Greene','India - West','gujarat' FROM dual
    UNION ALL
    SELECT 'Bella','Butler','India - West','gujarat' FROM dual
    UNION ALL
    SELECT 'Faith','Reid','India - North','delhi' FROM dual
    UNION ALL
    SELECT 'Jacob','Parsons','India - East','bihar' FROM dual
    UNION ALL
    SELECT 'Alan','Bailey','India - South','puducherry' FROM dual
    UNION ALL
    SELECT 'Katherine','Ferguson','India - East','bihar' FROM dual
    )
    select decode(grouping(Country_Region),1,'Grand Total',Country_Region) Region
       ,sum(gujarat_c) gujarat,sum(delhi_c) delhi,sum(bihar_c) bihar,sum(puducherry_c) puducherry
       ,sum(gujarat_c) +sum(delhi_c)+sum(bihar_c)+sum(puducherry_c) total
    from (select Country_Region,state from t) 
    pivot (count(*) c for state in ('gujarat' gujarat,'delhi' delhi,'bihar' bihar,'puducherry' puducherry))
    group by rollup(Country_Region)
    order by grouping(Country_Region),Country_Region
    /
    SQL> /
    
    
    REGION           GUJARAT      DELHI      BIHAR PUDUCHERRY      TOTAL
    ------------- ---------- ---------- ---------- ---------- ----------
    India - East           0          0         14          0         14
    India - North          0          7          0          0          7
    India - South          0          0          0          7          7
    India - West          12          0          0          0         12
    Grand Total           12          7         14          7         40
    
    
    
    
    Rengudi

Answers

  • Stax
    Stax Member Posts: 184 Silver Badge

    hi,Rengudi

    with t(First_Name,Last_Name,Country_Region,State) AS (
    SELECT 'Cameron' AS First_Name,'Greene' AS Last_Name,'India - East' AS Country_Region,'bihar' AS State FROM dual
    UNION ALL
    SELECT 'Dorothy','Clarkson','India - South','puducherry' FROM dual
    UNION ALL
    SELECT 'Kevin','Cornish','India - East','bihar' FROM dual
    UNION ALL
    SELECT 'Neil','Springer','India - West','gujarat' FROM dual
    UNION ALL
    SELECT 'Alison','Nash','India - West','gujarat' FROM dual
    UNION ALL
    SELECT 'Ian','Sutherland','India - North','delhi' FROM dual
    UNION ALL
    SELECT 'Diana','Thomson','India - East','bihar' FROM dual
    UNION ALL
    SELECT 'Gabrielle','Ross','India - South','puducherry' FROM dual
    UNION ALL
    SELECT 'Victor','Wright','India - East','bihar' FROM dual
    UNION ALL
    SELECT 'Neil','Young','India - West','gujarat' FROM dual
    UNION ALL
    SELECT 'Alexandra','Marshall','India - West','gujarat' FROM dual
    UNION ALL
    SELECT 'John','McLean','India - North','delhi' FROM dual
    UNION ALL
    SELECT 'Victor','Nash','India - East','bihar' FROM dual
    UNION ALL
    SELECT 'Alan','Piper','India - South','puducherry' FROM dual
    UNION ALL
    SELECT 'Amelia','Ross','India - East','bihar' FROM dual
    UNION ALL
    SELECT 'Ian','Pullman','India - West','gujarat' FROM dual
    UNION ALL
    SELECT 'Brandon','Berry','India - West','gujarat' FROM dual
    UNION ALL
    SELECT 'James','MacDonald','India - North','delhi' FROM dual
    UNION ALL
    SELECT 'Nicholas','Wallace','India - East','bihar' FROM dual
    UNION ALL
    SELECT 'Carolyn','Terry','India - South','puducherry' FROM dual
    UNION ALL
    SELECT 'Joan','Mathis','India - East','bihar' FROM dual
    UNION ALL
    SELECT 'Gavin','Peake','India - West','gujarat' FROM dual
    UNION ALL
    SELECT 'Victoria','Ince','India - West','gujarat' FROM dual
    UNION ALL
    SELECT 'Christopher','Underwood','India - North','delhi' FROM dual
    UNION ALL
    SELECT 'Stephanie','Oliver','India - North','delhi' FROM dual
    UNION ALL
    SELECT 'Tracey','James','India - East','bihar' FROM dual
    UNION ALL
    SELECT 'Sophie','Mackenzie','India - South','puducherry' FROM dual
    UNION ALL
    SELECT 'James','Knox','India - East','bihar' FROM dual
    UNION ALL
    SELECT 'Colin','Harris','India - West','gujarat' FROM dual
    UNION ALL
    SELECT 'Nathan','McLean','India - West','gujarat' FROM dual
    UNION ALL
    SELECT 'Nathan','Morrison','India - North','delhi' FROM dual
    UNION ALL
    SELECT 'Jack','North','India - East','bihar' FROM dual
    UNION ALL
    SELECT 'Anna','Walker','India - South','puducherry' FROM dual
    UNION ALL
    SELECT 'Natalie','Harris','India - East','bihar' FROM dual
    UNION ALL
    SELECT 'Piers','Greene','India - West','gujarat' FROM dual
    UNION ALL
    SELECT 'Bella','Butler','India - West','gujarat' FROM dual
    UNION ALL
    SELECT 'Faith','Reid','India - North','delhi' FROM dual
    UNION ALL
    SELECT 'Jacob','Parsons','India - East','bihar' FROM dual
    UNION ALL
    SELECT 'Alan','Bailey','India - South','puducherry' FROM dual
    UNION ALL
    SELECT 'Katherine','Ferguson','India - East','bihar' FROM dual
    )
    select decode(grouping(Country_Region),1,'Grand Total',Country_Region) Region
    ,count(decode(STATE,'gujarat',1)) gujarat
    ,count(decode(STATE,'delhi',1)) delhi
    ,count(decode(STATE,'bihar',1)) delhi
    ,count(decode(STATE,'puducherry',1)) puducherry
    ,count(decode(STATE,'gujarat',1)) 
    +count(decode(STATE,'delhi',1)) 
    +count(decode(STATE,'bihar',1)) 
    +count(decode(STATE,'puducherry',1)) total
    from t
    group by rollup(Country_Region)
    order by grouping(Country_Region),Country_Region
    /
    SQL> /
    
    
    REGION           GUJARAT      DELHI      DELHI PUDUCHERRY      TOTAL
    ------------- ---------- ---------- ---------- ---------- ----------
    India - East           0          0         14          0         14
    India - North          0          7          0          0          7
    India - South          0          0          0          7          7
    India - West          12          0          0          0         12
    Grand Total           12          7         14          7         40
    
    
    SQL>
    
    
    
  • Rengudi
    Rengudi Member Posts: 587 Bronze Badge

    Thanks! is it not achievable using PIVOT feature in oracle.?!

  • Frank Kulash
    Frank Kulash Member, Moderator Posts: 42,780 Red Diamond

    Hi, @Rengudi

    is it not achievable using PIVOT feature in oracle.?!

    Sure, here's one way:

    WITH    data_to_pivot    AS
    (
        SELECT    CASE
    		  WHEN  GROUPING (country_region) = 0
    		  THEN  country_region
    		  ELSE	'Grand Total'
    	      END			 AS country_region
        ,        CASE
    		  WHEN  GROUPING (state) = 0
    		  THEN  state
    		  ELSE	'total'
    	      END			 AS state
        ,	      COUNT (*)			 AS cnt
        ,	      GROUPING (country_region)  AS grpng
        FROM      input_table
        GROUP BY  CUBE (country_region, state)
    )
    SELECT    *	-- or list all columns except grpng
    FROM	  data_to_pivot
    PIVOT	  (    MIN (cnt)
    	  FOR  state  IN  ( 'bihar'	    AS bihar
    	       	      	  , 'delhi'	    AS delhi
    			  , 'gujarat'	    AS gujarat
    			  , 'puducherry'    AS puducherry
    			  , 'total'	    AS total
    			  )
    	  )
    ORDER BY  grpng
    ,     	  country_region
    ;
    
    
    

    The solution Stax posted is convenient because it handles NULLs the way you specified. Using SELECT ... PIVOT, that's an extra step.

  • mathguy
    mathguy Member Posts: 10,912 Black Diamond
    edited Nov 8, 2022 6:00PM

    You can do it with PIVOT, but the issue is that PIVOT doesn't mix well with the ROLLUP / CUBE options to aggregation.

    PIVOT is a somewhat limited aggregate operation. If you insist on using PIVOT, you could do what Mr. Kulash demonstrated - but that means you aggregate the data once (to get the counts by region and state), and then you aggregate again just to do the actual "pivoting".

    In such cases it is simpler to use traditional conditional aggregation - the way pivoting used to be done before Oracle introduced the PIVOT operator. I would do something like this:

    select case grouping(country_region) when 1 then '  GRAND TOTAL'
                         else country_region end          as region,
           count(case state when 'bihar'      then 1 end) as bihar,
           count(case state when 'delhi'      then 1 end) as delhi,
           count(case state when 'gujarat'    then 1 end) as gujarat,
           count(case state when 'puducherry' then 1 end) as puducherry,
           count(*) as grand_total
    from   input_table
    group  by rollup (country_region)
    order  by grouping(country_region), country_region
    ;
    
    REGION             BIHAR      DELHI    GUJARAT PUDUCHERRY GRAND_TOTAL
    ------------- ---------- ---------- ---------- ---------- -----------
    India - East          14          0          0          0          14
    India - North          0          7          0          0           7
    India - South          0          0          0          7           7
    India - West           0          0         12          0          12
      GRAND TOTAL         14          7         12          7          40
    

    The big question, of course, is whether you will be satisfied with this (or any other) solution. I expect your next question to be, "OK, but what if I don't know which states will be present in the data? I don't even know how many columns the output will have to have." So, please clarify that. Do you know, without looking at the data, which "states" must appear as columns in the output?

    The other question I have is: what's the purpose of this output? I assume a state can only be in one country-region, or are there some states that may be split between two (or more) regions? If they can't, an output as I show below makes much more sense to me:

    select case grouping(country_region)           when 1 then '  GRAND TOTAL'
                       else country_region end as country_region,
           case grouping_id(country_region, state) when 1 then '  REGION TOTAL'
                       else state          end as state,
           count(*) as ct
    from   input_table it
    group  by rollup(country_region, state)
    order  by grouping(it.country_region), country_region, grouping(it.state)
    ;
    
    COUNTRY_REGION  STATE                  CT
    --------------- -------------- ----------
    India - East    bihar                  14
    India - East      REGION TOTAL         14
    India - North   delhi                   7
    India - North     REGION TOTAL          7
    India - South   puducherry              7
    India - South     REGION TOTAL          7
    India - West    gujarat                12
    India - West      REGION TOTAL         12
      GRAND TOTAL                          40
    
    


    Of course, in your sample data you only have one state in each region; if you have, say, six states in each region, you will notice the big difference between this output format and the one you requested (which will have 26 columns in that case; who can read - and actually understand - a table with 26 columns?)

    One technical note - we must use qualified column names in ORDER BY to refer to the columns in the input table, not the columns in the output - when we use the same name in both. In the first example we used the column name REGION in the output, so there is no confusion with the column name in the input table; but when we use the same name in both, we must qualify the name in ORDER BY since ORDER BY can use columns both from the input table and from the output rowset.

  • Stax
    Stax Member Posts: 184 Silver Badge
    Answer ✓

    😉

    with t(First_Name,Last_Name,Country_Region,State) AS (
    SELECT 'Cameron' AS First_Name,'Greene' AS Last_Name,'India - East' AS Country_Region,'bihar' AS State FROM dual
    UNION ALL
    SELECT 'Dorothy','Clarkson','India - South','puducherry' FROM dual
    UNION ALL
    SELECT 'Kevin','Cornish','India - East','bihar' FROM dual
    UNION ALL
    SELECT 'Neil','Springer','India - West','gujarat' FROM dual
    UNION ALL
    SELECT 'Alison','Nash','India - West','gujarat' FROM dual
    UNION ALL
    SELECT 'Ian','Sutherland','India - North','delhi' FROM dual
    UNION ALL
    SELECT 'Diana','Thomson','India - East','bihar' FROM dual
    UNION ALL
    SELECT 'Gabrielle','Ross','India - South','puducherry' FROM dual
    UNION ALL
    SELECT 'Victor','Wright','India - East','bihar' FROM dual
    UNION ALL
    SELECT 'Neil','Young','India - West','gujarat' FROM dual
    UNION ALL
    SELECT 'Alexandra','Marshall','India - West','gujarat' FROM dual
    UNION ALL
    SELECT 'John','McLean','India - North','delhi' FROM dual
    UNION ALL
    SELECT 'Victor','Nash','India - East','bihar' FROM dual
    UNION ALL
    SELECT 'Alan','Piper','India - South','puducherry' FROM dual
    UNION ALL
    SELECT 'Amelia','Ross','India - East','bihar' FROM dual
    UNION ALL
    SELECT 'Ian','Pullman','India - West','gujarat' FROM dual
    UNION ALL
    SELECT 'Brandon','Berry','India - West','gujarat' FROM dual
    UNION ALL
    SELECT 'James','MacDonald','India - North','delhi' FROM dual
    UNION ALL
    SELECT 'Nicholas','Wallace','India - East','bihar' FROM dual
    UNION ALL
    SELECT 'Carolyn','Terry','India - South','puducherry' FROM dual
    UNION ALL
    SELECT 'Joan','Mathis','India - East','bihar' FROM dual
    UNION ALL
    SELECT 'Gavin','Peake','India - West','gujarat' FROM dual
    UNION ALL
    SELECT 'Victoria','Ince','India - West','gujarat' FROM dual
    UNION ALL
    SELECT 'Christopher','Underwood','India - North','delhi' FROM dual
    UNION ALL
    SELECT 'Stephanie','Oliver','India - North','delhi' FROM dual
    UNION ALL
    SELECT 'Tracey','James','India - East','bihar' FROM dual
    UNION ALL
    SELECT 'Sophie','Mackenzie','India - South','puducherry' FROM dual
    UNION ALL
    SELECT 'James','Knox','India - East','bihar' FROM dual
    UNION ALL
    SELECT 'Colin','Harris','India - West','gujarat' FROM dual
    UNION ALL
    SELECT 'Nathan','McLean','India - West','gujarat' FROM dual
    UNION ALL
    SELECT 'Nathan','Morrison','India - North','delhi' FROM dual
    UNION ALL
    SELECT 'Jack','North','India - East','bihar' FROM dual
    UNION ALL
    SELECT 'Anna','Walker','India - South','puducherry' FROM dual
    UNION ALL
    SELECT 'Natalie','Harris','India - East','bihar' FROM dual
    UNION ALL
    SELECT 'Piers','Greene','India - West','gujarat' FROM dual
    UNION ALL
    SELECT 'Bella','Butler','India - West','gujarat' FROM dual
    UNION ALL
    SELECT 'Faith','Reid','India - North','delhi' FROM dual
    UNION ALL
    SELECT 'Jacob','Parsons','India - East','bihar' FROM dual
    UNION ALL
    SELECT 'Alan','Bailey','India - South','puducherry' FROM dual
    UNION ALL
    SELECT 'Katherine','Ferguson','India - East','bihar' FROM dual
    )
    select decode(grouping(Country_Region),1,'Grand Total',Country_Region) Region
       ,sum(gujarat_c) gujarat,sum(delhi_c) delhi,sum(bihar_c) bihar,sum(puducherry_c) puducherry
       ,sum(gujarat_c) +sum(delhi_c)+sum(bihar_c)+sum(puducherry_c) total
    from (select Country_Region,state from t) 
    pivot (count(*) c for state in ('gujarat' gujarat,'delhi' delhi,'bihar' bihar,'puducherry' puducherry))
    group by rollup(Country_Region)
    order by grouping(Country_Region),Country_Region
    /
    SQL> /
    
    
    REGION           GUJARAT      DELHI      BIHAR PUDUCHERRY      TOTAL
    ------------- ---------- ---------- ---------- ---------- ----------
    India - East           0          0         14          0         14
    India - North          0          7          0          0          7
    India - South          0          0          0          7          7
    India - West          12          0          0          0         12
    Grand Total           12          7         14          7         40
    
    
    
    
    Rengudi