Is there a way to label the results of a GROUP BY GROUPING SETS
expression in Snowflake?
So, for example, If we have the following query
WITH data (product, customer, cost)
AS(
SELECT *
FROM VALUES
('Igloo', 'John', 5000) ,
('Igloo', 'John', 5000) ,
('Donkey', 'John', 1000) ,
('Donkey', 'Jane', 1000) ,
('Mortgage', 'John''s Brother, Jim', 1000)
)
SELECT product,
customer,
SUM(cost) AS sum_cost
FROM data
GROUP BY GROUPING SETS ((product), (customer), ())
ORDER BY GROUPING(product, customer);
I am wondering if Snowflake has a built-in method to generate an indicator column that gives you a guaranteed correct label for which group was used in the grouping sets.
I’d like to know if there is a different/better way to achieve this than using a CASE WHEN
clause which won’t necessarily give you a guaranteed correct result in all cases (I think).
Below, is an example of a generated indicator column using a CASE WHEN
statement for a simple example.
WITH data (product, customer, cost)
AS(
SELECT *
FROM VALUES
('Igloo', 'John', 5000) ,
('Igloo', 'John', 5000) ,
('Donkey', 'John', 1000) ,
('Donkey', 'Jane', 1000) ,
('Mortgage', 'John''s Brother, Jim', 1000)
)
SELECT product,
customer,
SUM(cost) AS sum_cost,
CASE
WHEN customer IS NULL AND product IS NOT NULL THEN 'PRODUCT'
WHEN product IS NULL AND customer IS NOT NULL THEN 'CUSTOMER'
WHEN customer IS NULL AND product IS NULL THEN ''
END AS indicator
FROM data
GROUP BY GROUPING SETS ((product), (customer), ())
ORDER BY GROUPING(product, customer);
If you’d have ten or twenty GROUPING SETS
, one can imagine a CASE WHEN
statement to not be a desirable/viable solution.
Any help is appreciated.