WITH ROLLUP in GROUP BY - SQL Server to PostgreSQL Migration

In SQL Server, you can use the legacy WITH ROLLUP clause in the GROUP BY clause of a SELECT statement. In PostgreSQL, you can use the GROUP BY ROLLUP 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 ROLLUP syntax:

SQL Server:

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

Using ISO-compliant GROUP BY ROLLUP clause:

SQL Server and PostgreSQL:

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

Result for both queries:

For more information, see SQL Server to PostgreSQL Migration.