Q54097267, Oracle - rextester

Language: Layout:

SELECT client_key, 
       CASE WHEN flag = 3 THEN 'AVG Totals -->' 
            WHEN flag = 2 THEN 'Grand Totals -->'
            ELSE client 
        END AS client , 
       SUM(spaceships_sold)/ DECODE(flag,3,3,1) AS spaceships_sold, 
       SUM(revenue)/ DECODE(flag,3,3,1) AS revenue
  FROM
  (
    WITH sample_data AS
    (
       SELECT 1 AS client_key, 'NASA'   AS client, 8 AS SPACESHIPS_SOLD, 105585 AS REVENUE FROM DUAL 
       UNION ALL
       SELECT 2 AS client_key, 'Origin' AS client, 3 AS SPACESHIPS_SOLD, 36581  AS REVENUE FROM DUAL 
       UNION ALL
       SELECT 3 AS client_key, 'SpaceX' AS client, 7 AS SPACESHIPS_SOLD, 83851  AS REVENUE FROM DUAL
     )
      SELECT sd.client_key, 
             nvl2(sd.client_key,client,null) AS client
           , SUM(sd.spaceships_sold) AS spaceships_sold
           , SUM(sd.revenue)         AS revenue
           , GROUPING_ID(sd.client_key, sd.client) AS flag
        FROM sample_data sd
       GROUP BY 
      GROUPING SETS (
                      (sd.client_key, sd.client),
                       (sd.client),()
                      )
    )    
  GROUP BY client_key, flag, client
  ORDER BY client_key, revenue desc


absolute service time: 1,19 sec