Tutorialsteacher

Follow Us

Articles
  • C#
  • C# OOP
  • ASP.NET Core
  • ASP.NET MVC
  • LINQ
  • Inversion of Control (IoC)
  • Web API
  • JavaScript
  • TypeScript
  • jQuery
  • Angular 11
  • Node.js
  • D3.js
  • Sass
  • Python
  • Go lang
  • HTTPS (SSL)
  • Regex
  • SQL
  • SQL Server
  • PostgreSQL
  • MongoDB
  • PostgreSQL - Get Started
  • Install PostgreSQL
  • Connect to PostgreSQL DB
  • Create Database
  • Create Table
  • Copy Table
  • Drop Table
  • Drop Database
  • Truncate Table
  • ALTER Table
  • Rename Table
  • Rename Columns
  • Add Columns
  • Modify Column Type
  • Set Default Value of Column
  • Remove Columns
  • Add Constraints to Table
  • Insert Data
  • Upsert Data
  • Update Data
  • Delete Data
  • SELECT Statement
  • WHERE Clause
  • GROUP BY Clause
  • HAVING Clause
  • ORDER BY Clause
  • DISTINCT Clause
  • Inner Join
  • Left Outer Join
  • Right Outer Join
  • Full Outer Join
  • Self Join
  • Natural Join
  • Cross Join
  • LIMIT OFFSET Clause
  • GROUPING SETS
  • GROUPING() Function
  • GROUP BY CUBE
  • GROUP BY ROLLUP
  • Sub Query
  • ALL Operator
  • ANY Operator
  • UNION Operator
  • INTERSECT Operator
  • EXCEPT Operator
  • IS NULL Operator
  • BETWEEN Operator
  • LIKE Operator
  • CAST Operator
  • CASE Expressions
  • NULLIF()
  • COALESCE()
  • GREATEST(), LEAST()
  • WITH Queries (CTE)
  • Constraints
  • NOT NULL Constraint
  • Unique Constraint
  • Check Constraint
  • Primary Key
  • Foreign Key
  • Sequence
  • Serial Type
  • Identity Columns
  • Generated Columns
  • Data Types
  • Boolean Type
  • Character Type
  • Integer Type
  • Numeric Type
  • Date Type
  • Time Type
  • TimeStamp Type
  • Interval Type
  • Array Type
  • Json Type
Entity Framework Extensions - Boost EF Core 9
  Bulk Insert
  Bulk Delete
  Bulk Update
  Bulk Merge

PostgreSQL GROUPING SETS

In PostgreSQL, the GROUPING SETS clause is a subclause of the GROUP BY clause.

The result of the SELECT and WHERE clauses are grouped separately by each specified group in the grouping set, and aggregates functions executed for each group same as simple GROUP BY clauses, and then the final results are returned.

The GROUPING SETS clause returns the same result as applying the UNION ALL operator on the multiple queries with GROUP BY clauses.

Syntax:
SELECT
    <column1>,
    <column2>
FROM
    <table_name>
GROUP BY
    GROUPING SETS (
        (column1, column2),
        (column1),
        (column2),
        ()
    )
[ORDER BY &lt;column_list<];

Let's see how to use the GROUPING SETS clause using the following employee table.

The following query uses the GROUP BY clause to return the sum of salary in each department. In other words, it defines a single grouping set on the dept_id column.

Example: GROUP BY
SELECT dept_id, SUM(salary)
FROM employee
GROUP BY dept_id;

The following query uses the GROUP BY clause to return the sum of salary for each gender. So it defines grouping set on the gender column.

Example: GROUP BY
SELECT gender, SUM(salary)
FROM employee
GROUP BY gender;

The following query uses the GROUP BY clause to return the sum of salary for each department and gender. So it defines grouping set on dept_id and gender columns.

Example: GROUP BY
SELECT dept_id, gender, SUM(salary)
FROM employee
GROUP BY dept_id, gender;

The following query fetch sum of salary of employees for all department and gender. So it defines a grouping set which is denoted by ().

Now if you want all the above result sets in a single query, you can use the UNION ALL operator to combine result sets. As UNION ALL operator requires all result sets to have the same number of columns and compatible data types, you need to add NULL to the selection list of each query as below.

Example: UNION ALL
SELECT dept_id, gender, SUM(salary) FROM employee GROUP BY dept_id, gender
UNION ALL
    SELECT dept_id, NULL, SUM(salary) FROM employee GROUP BY dept_id
UNION ALL
    SELECT NULL, gender, SUM(salary) FROM employee GROUP BY gender
UNION ALL
    SELECT NULL, NULL, SUM(salary) FROM employee;

Although the above query works as expected, it's very lengthy and it can give performance issues as multiple times data is selected from the employee table.

The same result can be achieved by using the GROUPING SETS clause which is a subset of the GROUP BY clause. Here the query is much shorter and returns the same result.

Example: GROUPING SETS
SELECT dept_id, gender, SUM(salary) FROM employee 
GROUP BY
	GROUPING SETS (
		(dept_id, gender),
		(dept_id),
		(gender),
		()
	);
TUTORIALSTEACHER.COM

TutorialsTeacher.com is your authoritative source for comprehensive technologies tutorials, tailored to guide you through mastering various web and other technologies through a step-by-step approach.

Our content helps you to learn technologies easily and quickly for learners of all levels. By accessing this platform, you acknowledge that you have reviewed and consented to abide by our Terms of Use and Privacy Policy, designed to safeguard your experience and privacy rights.

[email protected]

ABOUT USTERMS OF USEPRIVACY POLICY
copywrite-symbol

2024 TutorialsTeacher.com. (v 1.2) All Rights Reserved.