Table of Contents Previous Next



2.4.11.1 LISTAGG
Advanced Server has added the LISTAGG function to support string aggregation. LISTAGG is an aggregate function that concatenates data from multiple rows into a single row in an ordered manner. You can optionally include a custom delimiter for your data.
The LISTAGG function mandates the use of an ORDER BY clause under a WITHIN GROUP clause to concatenate values of the measure column, and then generate the ordered aggregated data.
LISTAGG can be used without any grouping. In this case, the LISTAGG function operates on all rows in a table and returns a single row.
LISTAGG can be used with the GROUP BY clause. In this case, the LISTAGG function operates on each group and returns an aggregated output for each group.
LISTAGG can be used with the OVER clause. In this case, the LISTAGG function partitions a query result set into groups based on the expression in the query_partition_by_clause and then aggregates data in each group.
LISTAGG( measure_expr [, delimiter ]) WITHIN GROUP( order_by_clause ) [ OVER query_partition_by_clause ]
measure_expr (mandatory) specifies the column or expression that assigns a value to aggregate. NULL values are ignored.
delimiter (optional) specifies a string that separates the concatenated values in the result row. The delimiter can be a NULL value, string, character literal, column name, or constant expression. If ignored, the LISTAGG function uses a NULL value by default.
order_by_clause (mandatory) determines the sort order in which the concatenated values are returned.
query_partition_by_clause (optional) allows LISTAGG function to be used as an analytic function and sets the range of records for each group in the OVER clause.
The LISTAGG function returns a string value.
The following example concatenates the values in the EMP table and lists all the employees separated by a delimiter comma.
First, create a table named EMP and then insert records into the EMP table.
The following example uses PARTITION BY clause with LISTAGG in EMP table and generates output based on a partition by DEPTNO that applies to each partition and not on the entire table.
2.4.11.2 MEDIAN
The MEDIAN function that calculates the middle value of an expression from a given range of values; NULL values are ignored. The MEDIAN function returns an error if a query does not reference the user-defined table.
MEDIAN can be used without any grouping. In this case, the MEDIAN function operates on all rows in a table and returns a single row.
MEDIAN can be used with the OVER clause. In this case, the MEDIAN function partitions a query result set into groups based on the expression specified in the PARTITION BY clause and then aggregates data in each group.
MEDIAN( median_expression ) [ OVER ( [ PARTITION BY... ] ) ]
median_expression (mandatory) is a target column or expression that the MEDIAN function operates on and returns a median value. It can be a numeric, datetime, or interval data type.
PARTITION BY clause (optional) allows a MEDIAN function to be used as an analytic function and sets the range of records for each group in the OVER clause.
The return type is determined by the input data type of expression. The following table illustrates the return type for each input type.
The following example uses PARTITION BY clause with MEDIAN in EMP table and returns the median salary based on a partition by DEPTNO:
The MEDIAN function can be compared with PERCENTILE_CONT. In the following example, MEDIAN generates the same result as PERCENTILE_CONT:


Table of Contents Previous Next