WITH CUBE in GROUP BY - SQL Server to PostgreSQL Migration

In SQL Server, you can use the legacy WITH CUBE clause in the GROUP BY clause of a SELECT statement.

In PostgreSQL, you can use the GROUP BY CUBE clause, which is also supported by SQL Server and is ISO-compliant.

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);

A sample query with a legacy WITH CUBE syntax:

SQL Server:

  -- Get summary items for products 
  SELECT product, category, SUM(items) 
  FROM orders
  GROUP BY product, category WITH CUBE;

Using ISO-compliant GROUP BY CUBE clause:

SQL Server and PostgreSQL:

    -- Get summary items for products 
  SELECT product, category, SUM(items) 
  FROM orders
  GROUP BY CUBE (product, category);

The result for both queries:

For more information, see SQL Server to PostgreSQL Migration.