Search code examples
sqloracle-databaseoracle11ggroup-bygrouping-sets

How to create two Grand Total rows using SQL - Totals and Averages

I am needing to create two rows that contain Totals as opposed to the typical one totals row. Grand Totals and Averages.

I am creating a report using basic SQL and I am working out of an Oracle database but I am not using any PL/SQL.

I'm currently using Group By Grouping Sets to produce a report and one row is the row that contains the Grand Totals. These totals are currently being produced using SUM(column) using a mix of aggregate and analytical functions to produce my one row of Grand Totals. What I need is another row that produces totals on the same data set. What is the best way to accomplish this? When I say best I'm thinking of the load on my database as this report will be running against a ton of data. My examples are extremely basic but get the point across.

Below is some sample data that produces Summed Grand Totals using Group By Grouping Sets. What's missing is another row below the Grand Totals that I would like to produce Averages.

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
  , CASE WHEN grouping(sd.client) = 0 THEN to_char(sd.client) ELSE 'Grand Totals -->' END AS client
  , SUM(sd.spaceships_sold) AS spaceships_sold
  , SUM(sd.revenue)         AS revenue
FROM sample_data sd
GROUP BY 
  GROUPING SETS (
                  (sd.client_key, sd.client),
                  ()
                )
;

Example Image of what I'm looking for.

enter image description here

Below are my thoughts as to how I can get this extra Totals Row but not sure if this is what I should be doing in order to obtain this. Seems convoluted and I keep thinking that this should be an existing feature of Grouping Sets. In the below approach I'm using CTE's and UNION ALL to get the extra Averages Totals at the bottom of my data set as seen in the screenshot below.

enter image description here

SQL from the above screenshot.

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
)

, data_Sum_totals AS
(
  SELECT sd.client_key
    , CASE WHEN grouping(sd.client) = 0 THEN to_char(sd.client) ELSE 'Grand Totals -->' END AS client
    , SUM(sd.spaceships_sold) AS spaceships_sold
    , SUM(sd.revenue)         AS revenue
  FROM sample_data sd
  GROUP BY 
    GROUPING SETS (
                    (sd.client_key, sd.client),
                    ()
                  )
)

, data_Avg_totals AS
(
  SELECT grouping(sd.client_key) AS row_group
    , sd.client_key
    , CASE WHEN grouping(sd.client) = 0 THEN to_char(sd.client) ELSE 'AVG Totals -->' END AS client
    , AVG(sd.spaceships_sold) AS spaceships_sold
    , AVG(sd.revenue)         AS revenue
  FROM sample_data sd
  GROUP BY 
    GROUPING SETS (
                    (sd.client_key, sd.client),
                    ()
                  )
  HAVING grouping(sd.client_key) = 1 /* This line restricts the output to only give me the Totals row */
)

SELECT client_key, client, spaceships_sold, revenue
FROM data_Sum_totals
  UNION ALL
SELECT client_key, client, spaceships_sold, revenue
FROM data_Avg_totals
;
Solution

  • You pointed out :

    I keep thinking that this should be an existing feature of Grouping Sets. In the below approach I'm using CTE's and UNION ALL to get the extra Averages Totals at the bottom of my data set as seen in the screenshot below

    and How the [grouping-sets] tag is defined :

    The GROUPING SETS operator is an extensions of the GROUP BY clause. It can generate the same result set as when you use UNION ALL to combine single grouping queries; however, using GROUPING SETS operator is usually more efficient.

    Therefore, you had such a nice approach.

    I think using GROUPING_ID suits best for your case as in the following SQL statement :

    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;



  CLIENT_KEY    CLIENT           SPACESHIPS_SOLD    REVENUE
  -----------   ---------------- ---------------   --------
       1        NASA                   8             105585
       2        Origin                 3              36581
       3        SpaceX                 7              83851
      NULL      Grand Totals -.->     18             226017
      NULL      AVG Totals -.->        6              75339

    Rextester Demo

    Update to SQL to work with any number or records aka clients

    SELECT client_key, 
     CASE WHEN flag = 3 THEN 'AVG Totals -->' 
          WHEN flag = 2 THEN 'Grand Totals -->'
          ELSE client 
      END AS client 
      , flag,
     SUM(spaceships_sold)/ DECODE(flag,3,tot_clients,1) AS spaceships_sold, 
     SUM(revenue)/ DECODE(flag,3,tot_clients,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
     UNION ALL
     SELECT 4 AS client_key, 'Comp'   AS client, 4  AS SPACESHIPS_SOLD, 95823   AS REVENUE FROM DUAL
     UNION ALL
     SELECT 4 AS client_key, 'CNSA'   AS client, 11 AS SPACESHIPS_SOLD, 135851  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
         , COUNT(sd.client_key)    AS tot_clients
         , 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, tot_clients
ORDER BY client_key, revenue desc
;