GROUPING SETS in SELECT Statements - SQL Server to PostgreSQL Migration

In SQL Server and PostgreSQL, the GROUPING SETS clause of the GROUP BY clause of a SELECT statement allows you to define one or more GROUP BY expressions.

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:

SQL Server and PostgreSQL:

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

Result:

For more information, see SQL Server to PostgreSQL Migration.