PostgreSQL GROUPING() function

The GROUPING() function is used in conjunction with the GROUPING SETS clause to distinguish result rows.

The arguments to the GROUPING function must exactly match expressions given in the GROUP BY clause. However, they are not evaluated.

The GROUPING() function returns 0 or 1. It returns 0 if the argument is a member of the current grouping set; otherwise returns 1.

The following query demonstrates the GROUPING() function.

Example: GROUPING()

SELECT GROUPING(dept_id) grouping_dept_id,
	GROUPING(gender) grouping_gender,dept_id, gender, SUM(salary) FROM employee 
GROUP BY
	GROUPING SETS (
		(dept_id),
		(gender),
		()
    )
ORDER BY dept_id, gender;

As shown above, in maroon, when the value in grouping_dept_id is 0, the sum shows the subtotal group by dept_id. When the value in grouping_gender is 0, the sum shows the subtotal group by gender. The last row shows the total sum of salary, so it has grouping_dept_id and grouping_gender both as 1.

The GROUPING function can be used in the HAVING clause to filter out data as shown below.

Example: GROUPING() with HAVING

SELECT GROUPING(dept_id) grouping_dept_id,
	GROUPING(gender) grouping_gender,dept_id, gender, SUM(salary) FROM employee 
GROUP BY
	GROUPING SETS (
		(dept_id),
		(gender),
		()
    )
HAVING GROUPING(dept_id) = 0
ORDER BY dept_id, gender;