PostgreSQL: Aggregate Functions

I kind of skipped over the functions/operators section of the PostgreSQL documentation when reading it. I want to review the sections that I think would be useful to me.

Date Created:

1 17

Notes

Aggregate functions compute a single result from a set of unique values.


Function	Description
`array_agg(expression)`	input values, including nulls, concatenated into array
`avg(expression)`	the average (arithmetic mean) of all non-null input values
`count(expression)`	number of input rows for which the value of expression is not null
`every(expression)`	the bitwise AND of all non-null input values, or null if none
`json_agg(expression)`	aggregate values, including nulls, as a JSON array
`json_object_agg(name,value)`	aggregates name/value pairs as a JSON object; values can be null, but not names
`max(expression)`	maximum value of expression across all non-null input values
`min(expression)`	minimum value of expression across all non-null input values
`string_agg(expression,delimeter)`	non-null input values concatenated into a string, separated by delimiter
`sum(expression)`	sum of expression across all non-null input values
`xmlagg(expression)`	concatenation of non-null XML values

It should be noted that except for count, these functions return a null value when no rows are selected. The coalesce function can be used to substitute zero or an empty array for null when necessary.

The aggregate functions array_agg, json_agg, jsonb_agg, json_object_agg string_agg, and xml_agg, as well as similar user-defined functions, produce meaningfully different results depending on the order of the input values. This ordering is unspecified by default, but can be controlled by writing an ORDER BY clause within the aggregating call. Alternatively, supplying the input values from a sorted subquery will usually work.

SELECT xmlagg(x) FROM (SELECT x FROM test ORDER BY y DESC) AS tab;

This syntax is not allowed in the SQL standard, and is not portable to other database systems.

PostgreSQL: Aggregate Functions

References

Notes

Comments

User Comments

Inserting Custom HTML:

Basics:

Inserting Code Expressions:

Inserting $\Large{LaTeX}$ Expressions:

SVG Icon Search

AI Code Generation

The Sanitization of HTML