CODEWITHSUNDEEP
sqloracle-databaseoracle11ggroup-bygrouping-sets
Code Novice
Code Novice

Reputation: 2398

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
;

Upvotes: 1

Views: 1431

Answers (2)

Barbaros Özhan
Barbaros Özhan

Reputation: 65218

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
;

Upvotes: 3

Mark Moretto
Mark Moretto

Reputation: 2348

CTEs are window functions, so they aren't able to be carried down as far as you expect. For this problem, I think you have a good idea, but would probably just use a couple of temporary tables to house specific data, then UNION everything together at the end.

Here's the query I came up with:

-- Clear out temporary tables
IF OBJECT_ID('tempdb.dbo.#SampleData') IS NOT NULL DROP TABLE #SampleData
IF OBJECT_ID('tempdb.dbo.#TotTable') IS NOT NULL DROP TABLE #TotTable
IF OBJECT_ID('tempdb.dbo.#AvgTable') IS NOT NULL DROP TABLE #AvgTable

-- Create
DECLARE @_tot INT
DECLARE @_avg NUMERIC(18,2)
DECLARE @client_count INT

-- Sample Data
CREATE TABLE #SampleData (
    [CLIENT_KEY] INT,
    [CLIENT] NVARCHAR(10),
    [SPACESHIPS_SOLD] VARCHAR(10),
    [REVENUE] VARCHAR(25)
)

INSERT INTO #SampleData
VALUES (1,'NASA','8','105585'),
        (2,'Origin','3','36581'),
        (3,'SpaceX','7','83851')


-- Get our total numbers
SELECT 'Grand Totals' AS [Name],
SUM(CONVERT(INT, [REVENUE])) AS [Total_Rev],
SUM(CONVERT(INT, [SPACESHIPS_SOLD])) AS [Ships_Sold] 
INTO #TotTable
FROM #SampleData

-- Get our average numbers
SET @client_count = (SELECT COUNT([CLIENT]) FROM #SampleData)
SELECT 'AVG Totals' AS [Name],
SUM(CONVERT(INT, [REVENUE])) / COUNT(*) AS [Avg_Rev],
SUM(CONVERT(INT, [SPACESHIPS_SOLD])) / @client_count AS [Avg_Sold]
INTO #AvgTable
FROM #SampleData

-- Union it all together
SELECT
    [CLIENT_KEY],
    [CLIENT],
    [SPACESHIPS_SOLD],
    [REVENUE]
FROM #SampleData
UNION ALL
SELECT
    NULL AS [CLIENT_KEY],
    [Name] AS [CLIENT],
    [Ships_Sold]  [SPACESHIPS_SOLD],
    [Total_Rev] AS [REVENUE]
FROM #TotTable
UNION ALL
SELECT
    NULL AS [CLIENT_KEY],
    [Name] AS [CLIENT],
    [Avg_Sold]  [SPACESHIPS_SOLD],
    [Avg_Rev] AS [REVENUE]
FROM #AvgTable

--Clear out tables (not necessary, but nice to do)
IF OBJECT_ID('tempdb.dbo.#SampleData') IS NOT NULL DROP TABLE #SampleData
IF OBJECT_ID('tempdb.dbo.#TotTable') IS NOT NULL DROP TABLE #TotTable
IF OBJECT_ID('tempdb.dbo.#AvgTable') IS NOT NULL DROP TABLE #AvgTable

Upvotes: 1

Related Questions