SQL GROUPING SETS Alternative in Synapse

  • Post author:
  • Post last modified:September 15, 2022
  • Post category:Azure Synapse
  • Reading time:4 mins read

The GROUPING SETS option in SQL gives you the ability to combine multiple GROUP BY clauses into one GROUP BY clause. By definition, a grouping set is a group of columns by which you group. Azure Synapse SQL data warehouse does not support GROUPING SETS. In this article, we will check this SQL GROUPING SETS alternative in Azure Synapse SQL data warehouse with an example.

SQL GROUPING SETS Alternative in Synapse

As mentioned, GROUPING SETS will combine multiple GROUP BY clauses into one GROUP BY clause. 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, the following query defines the grouping sets that include country and city. This query will execute without any issues on databases such as Teradata. 

SELECT county, city, sum(margin) 
   FROM sale_view
   GROUP BY  GROUPING SETS ((county),(city))
   order by 1,2 ;

However, same query will not execute on Azure Synapse. You will end getting error something like “Incorrect syntax near ‘sets’.” . It is because Synapse does not support grouping sets.

GROUPING SETS Alternative using UNION ALL

As mentioned, to get a same result set with the aggregated data for all grouping sets, you can use the UNION ALL operator.

Since UNION ALL operator requires all result set to have the same number of columns, you need to add NULL to the select list to the queries similar to the one in following example.

SELECT county,NULL, sum(margin) 
   FROM dbo.sale_view
   GROUP BY county
union all 
SELECT NULL, city, sum(margin) 
   FROM dbo.sale_view
   GROUP BY city
ORDER BY  1, 2  
   ;

The query will generate a single result with the aggregates for all grouping sets.

However, following are some of the drawbacks of using the UNION ALL method.

  • The query will be lengthy as we are breaking the query into multiple subqueries
  • The query will be slow. The Synapse database needs to execute multiple subqueries and combine result sets.

Related Articles,

Hope this helps 🙂

Tags: , ,