CODEWITHSUNDEEP
snowflake-cloud-data-platform
Py1996
Py1996

Reputation: 239

Aggregation(SUM) after group by for multiple levels using snowflake Query

I have a Table where i need to perform aggregation. Example please refer below table

Region  Country State   City    ValueX  ValueY  SEC
A          B      D      1        10     10     aaa
A          B      D      2        10     10     bbb
A          B      E      3        10     10     ccc
A          C      F      4        10     10     ddd
A          C      F      5        10     10     eee
A          C      G      6        10     10     fff

From the above table I need SUM of values after group by for columns Region, Country, State

Expected output table

Place   ValueX  ValueY  SEC
D        20       20    aaa
D        20       20    bbb
E        10       10    ccc
F        20       20    ddd
F        20       20    eee
G        10       10    fff
B        30       30    aaa
B        30       30    bbb
B        30       30    ccc
C        30       30    ddd
C        30       30    eee
C        30       30    fff
A        60       60    aaa
A        60       60    bbb
A        60       60    ccc
A        60       60    ddd
A        60       60    eee
A        60       60    fff

I tried to use groupby with SUM function but this gives me a single row and I have no idea on how this value can be populated on table.

CREATE OR REPLACE TABLE MY_TABLE (
  Region   VARCHAR
 ,Country  VARCHAR
 ,State  VARCHAR
 ,City  INT
 ,ValueX INT
 ,ValueY INT
 ,SEC VARCHAR
)
AS
  SELECT *
    FROM VALUES
           ('A','B','D',1,10,10,'aaa')
          ,('A','B','D',2,10,10,'bbb')
          ,('A','B','E',3,10,10,'ccc')
          ,('A','C','F',4,10,10,'ddd')
          ,('A','C','F',5,10,10,'eee')
          ,('A','C','G',6,10,10,'fff')
;

SELECT State, SUM(ValueX) FROM MY_TABLE GROUP BY State;
SELECT State, SUM(ValueY) FROM MY_TABLE GROUP BY State;

Please suggest if there is any easy method.

Upvotes: 0

Views: 2823

Answers (1)

Michael Golos
Michael Golos

Reputation: 2059

You should be interested in the syntax: GROUP BY GROUPING SETS, GROUP BY CUBE and GROUP BY ROLLUP

Solution proposals:

GROUP BY GROUPING SETS

WITH cte AS (
  SELECT COALESCE(State, Country, Region) AS Place, SUM(ValueX) AS ValueX, SUM(ValueY) AS ValueY
    FROM MY_TABLE
   GROUP BY GROUPING SETS (Region, Country, State)
)
SELECT T1.Place, T1.ValueX, T1.ValueY, T2.SEC
  FROM cte AS T1
  LEFT JOIN MY_TABLE AS T2 ON T1.Place = T2.State OR  T1.Place = T2.Country OR T1.Place = T2.Region;

GROUP BY CUBE

WITH cte AS (
  SELECT COALESCE(State, Country, Region) AS Place, SUM(ValueX) AS ValueX, SUM(ValueY) AS ValueY  
    FROM MY_TABLE
   GROUP BY CUBE (Region, Country, State)
)
SELECT DISTINCT T1.Place, T1.ValueX, T1.ValueY, T2.SEC
  FROM cte AS T1
  LEFT JOIN MY_TABLE AS T2 ON T1.Place = T2.State OR  T1.Place = T2.Country OR T1.Place = T2.Region
 WHERE T1.Place IS NOT NULL;

GROUP BY ROLLUP

WITH cte AS (  
  SELECT COALESCE(State, Country, Region) AS Place, SUM(ValueX) AS ValueX, SUM(ValueY) AS ValueY
    FROM MY_TABLE
   GROUP BY ROLLUP  (Region, Country, State)
)
SELECT T1.Place, T1.ValueX, T1.ValueY, T2.SEC
  FROM cte AS T1
  LEFT JOIN MY_TABLE AS T2 ON T1.Place = T2.State OR  T1.Place = T2.Country OR T1.Place = T2.Region
 WHERE T1.Place IS NOT NULL;

Upvotes: 2

Related Questions