Aggregation functions

Aggregation functions operate on arrays of data and produce a single numerical result.

Logical operations (BOOL_OR, BOOL_AND)

Sometimes you need to find out if all values of a boolean variable by group are true. For such cases you can use boot_and aggregation function. Also there is function bool_or if you need to get if any value by group is true.

---

The following example presents a comprehensive list of all possible pairings between logical values and the functions we’re exploting are applied to them.

--postgreSQL
SELECT
    bool_and(all_true) AS all_true_and,
    bool_or(all_true) AS all_true_or,
    bool_and(one_true1) AS one_true1_and,
    bool_or(one_true1) AS one_true1_or,
    bool_and(one_true2) AS one_true2_and,
    bool_or(one_true2) AS one_true2_or,
    bool_and(no_true) AS no_true_and,
    bool_and(no_true) AS no_true_or
FROM (
    VALUES 
        (TRUE, TRUE, FALSE, FALSE), 
        (TRUE, FALSE, TRUE, FALSE)
) AS temp(all_true, one_true1, one_true2, no_true);

SELECT 1

all_true_and	all_true_or	one_true1_and	one_true1_or	one_true2_and	one_true2_or	no_true_and	no_true_or
True	True	False	True	False	True	False	False

Strings concatenations

If you need to combine the values of a string variable into a single string, you can use the string_agg(<column for concatenation>, <separator>) aggregation function. This function will output all the values in a single line, separated by the specified separator.

---

The following example concatenates strings ‘one’, ‘two’ and ‘three’.

--postgreSQL
SELECT
    string_agg(string_variable, ', ')
FROM (
    VALUES
    ('one'), ('two'), ('three')
) AS temp(string_variable)

SELECT 1

string_agg
one, two, three

Collect array

There is a function that allows transforming a set of numbers into an array that fits in one cell of a table. In PostgreSQL, this can be done using the array_agg function.

---

The following cell shows the application of the array_agg function to the some_value column, excluding a particular element from the array.

--postgreSQL
SELECT
    array_agg(some_value) whole_array,
    (array_agg(some_value))[1] first_element,
    (array_agg(some_value))[2] second_element,
    (array_agg(some_value))[3] third_element
FROM (
    VALUES
    (4), (3), (3), (1), (3), (3), (3), (5), (5), (1)
) AS temp(some_value);

SELECT 1

whole_array	first_element	second_element	third_element
[4, 3, 3, 1, 3, 3, 3, 5, 5, 1]	4	3	3