GROUPING_ID Function - SQL Server to PostgreSQL Migration

In SQL Server, the GROUPING_ID function returns the GROUPING SETS level of GROUP BY using a bit mask with 1 value in the corresponding bit position if the column is not used in GROUP BY for the resulting aggregated row.

In PostgreSQL, you can use the GROUPING function.

Consider a sample table:

  CREATE TABLE orders 
  (
     product VARCHAR(30),
     category VARCHAR(30),
     items INT
  );  
 
  -- Sample rows
  INSERT INTO orders VALUES ('Apple', 'Fruits', 1);
  INSERT INTO orders VALUES ('Orange', 'Fruits', 3);
  INSERT INTO orders VALUES ('Apple', 'Fruits', 7);
  INSERT INTO orders VALUES ('Milk', 'Diary', 5);

Consider the following query with GROUPING SETS and GROUPING_ID function:

SQL Server:

  -- Get summary items for products within a category, for the entire category and total summary
  SELECT product, category, SUM(items) AS items, GROUPING_ID(product, category) AS level
  FROM orders
  GROUP BY GROUPING SETS ((product, category),(category), ());

PostgreSQL:

  -- Get summary items for products within a category, for the entire category and total summary
  SELECT product, category, SUM(items) AS items, GROUPING(product, category) AS level
  FROM orders
  GROUP BY GROUPING SETS ((product, category),(category), ());

Result for both queries:

For example, for Milk, Diary row, level 0 is returned because both product and category were used in GROUP BY producing this row. For NULL, Diary row, level 2 (b'10') is returned because product was not used in GROUP BY. And for NULL, NULL row, level 3 (b'11') is returned because none of columns were used for this aggregate row.

For more information, see SQL Server to PostgreSQL Migration.