SQL Reference - WITHIN GROUP

WITHIN GROUP

WITHINGROUP is a SQL function used in aggregate functions such as Percentile_Cont and Percentile_Disc. It helps to order the row values of a particular column to apply aggregate functions. Typically, the values are ordered for categorization purposes within a defined grouping set. It is important to note, WITHIN_GROUP can only modify the behavior of other functions. It cannot operate on its own.

WITHIN GROUP (ORDER BY expression)

within group: This keyword in PostgreSQL is used to order or sort the partitioned data within a group. Within each group, this function sorts the data based on the expression provided.
order by expression: It is the sorting order that is specified. Data can be sorted in ascending or descending order based on numeric value, string value, date value, etc. This ordering is done as per the criteria specified in the expression.

Example

SELECT gender,
       avg(age) WITHIN GROUP (ORDER BY age) as average_age
FROM   employee
GROUP  BY gender;

Output

 gender | average_age
--------+-------------
 male   |       35.4
 female |       33.6

Explanation

In the code example, the WITHIN_GROUP function is used in conjunction with the avg function to calculate the ordered average age within each gender group from the employee table. The results are then grouped by the gender column. Network traffic is reduced because the sorted results are retrieved directly from the server.

WITHIN GROUP ( aggregate_function ORDER BY order_by_clause )

aggregate_function: This is a function that performs a calculation on a set of values and returns a single output value. Examples of common aggregate functions in SQL include SUM(), AVG(), MAX(), MIN(), and COUNT(). The aggregate function is applied on the data contained within each group specified by the WITHIN_GROUP statement.
order by clause: This specifies the sort order used when carrying out the aggregation. The data is sorted based on this clause before applying the aggregate function. It can be used to sort the data in ascending (ASC) or descending (DESC) order.

Example

SELECT
  percentile_cont(0.5) WITHIN GROUP (ORDER BY salary) AS median_salary
FROM employees

Output

"median_salary"": 4750.00

Explanation

The above SQL query calculates the median salary for employees using the PERCENTILE_CONT function. It orders the salaries in ascending order and returns the median (i.e., 50th percentile) value.