CODEWITHSUNDEEP
arrayspostgresql
mick
mick

Reputation: 51

Postgresql array sum

Given an array column in a table of a PostgreSQL database containing the following:

{{765,4},{767,3},{569,5},{567,3},{725,5}}

How could I calculate the sum of all second elements of each subarray, i.e. 4+3+5+3+5

Upvotes: 2

Views: 1385

Answers (2)

mick
mick

Reputation: 51

I was able to resolve my objective presented here by using jsonb array.
The jsonbArray
[{"an": 4, "qid": 765}, {"an": 3, "qid": 767}, {"an": 5, "qid": 569}, {"an": 3, "qid": 567}, {"an": 5, "qid": 725}]

The query that accomplishes the objective:

WITH answers as (
    SELECT
        (jsonbArray -> 'an')::int as an,
        (jsonbArray -> 'qid')::int as qid
    FROM (
        SELECT jsonb_array_elements(jsonbArray) AS jsonbArray
        FROM user where id = 1
        ) AS s
    group by qid, an
    )
select sum(an) as score from answers where qid in (765,725)

Result: 
score
9

Upvotes: 0

clemens
clemens

Reputation: 17721

You can try using UNNEST which expands an array to a set of rows, and filtering by the row number:

SELECT *, (
    SELECT SUM(v) 
    FROM UNNEST(array_column) WITH ORDINALITY a(v, n) 
    WHERE n % 2 = 0
) FROM your_table;

Upvotes: 2

Related Questions