SQL GROUP BY with CUBE Function Alternative in Synapse

Post author:Vithal S
Post last modified:July 12, 2022
Post category:Azure Synapse
Reading time:4 mins read

Many relational databases such as Oracle, Teradata, etc. support GROUP BY with CUBE function to group the result rows. However, Azure Synapse does not support GROUP BY with CUBE function. In this article, we will check the SQL GROUP BY CUBE function alternative in the Azure Synapse data warehouse.

SQL GROUP BY with CUBE Function Alternative in Synapse

Similar to the ROLLUP function, CUBE is an extension of the GROUP BY clause. The CUBE allows you to generate subtotals like the ROLLUP extension. In addition, the CUBE extension will generate subtotals for all combinations of grouping columns specified in the GROUP BY clause.

For example, the following query defines the group by with CUBE function that include pid and county. This query will execute without any issues on databases such as Teradata.

SELECT pid, county, SUM(sale) as total_sale
FROM sale_view
GROUP BY CUBE (pid,county)
order by 1,2;

But, same query will not work on the Azure synapse data warehouse. When you try to execute above query, you will end up getting “‘CUBE’ is not a recognized built-in function name.” error on Azure Synapse. It is because Synapse does not support SQL GROUP BY with CUBE Function.

GROUP BY with CUBE Function Alternative Using UNION ALL

It’s possible to create a GROUP BY WITH CUBE using the UNION ALL approach. The GROUP BY with CUBE function will combine multiple GROUP BY clauses. The results are the equivalent of UNION ALL of the specified groups. We will rewrite original query using the UNION ALL set operator to return the same result set.

For example, following query combines the multiple GROUP BY clause.

select pid, county, SUM(sale) as total_sale
from sale_view 
group by pid, county
UNION ALL
select pid, null as county, sum(sale) as total_sale
from sale_view  
group by pid
UNION ALL
select NULL as pid ,county, sum(sale) as total_sale
from sale_view 
group by county
UNION ALL
select NULL,NULL, sum(sale) as total_sale
from sale_view 
order by 1,2;

The approach works well if you have predictable columns in the CUBE function.

However, with the above approach, the code can quickly become cumbersome and unwieldy. You can use a more advance approach which is mentioned in official documentation.