CODEWITHSUNDEEP
postgresqlpostgresql-9.1
Nicolas
Nicolas

Reputation: 2367

Select sum of an array column in PostgreSQL

If I have the following table:

Table "users"
Column          |       Type       | Modifiers 
---------------+------------------+-----------
  id            | integer          | 
  monthly_usage | real[]           |

Where monthly_usage is an array of 12 numbers, i.e. {1.2, 1.3, 6.2, 0.9,...}

How can I select the sum of that column?

Something along the lines of:

SELECT id, sum(monthly_usage) as total_usage from users;

Which obviously doesn't work.

Upvotes: 30

Views: 34997

Answers (4)

Alexi Theodore
Alexi Theodore

Reputation: 1677

If you're looking for a more "functional" solution, this works well for any sum-able quantity:

CREATE OR REPLACE FUNCTION array_sum(
    IN array_in ANYARRAY
,   OUT array_sum ANYELEMENT
) AS
$$
DECLARE
BEGIN

SELECT
    sum(a)
INTO array_sum
FROM unnest(array_in) a
;

END
$$
LANGUAGE plpgsql
IMMUTABLE
;

examples:

array_sum('{1,2,3}'::INT[])

6

array_sum('{1.1,2.2,3.3}'::NUMERIC[])

6.6

array_sum('{1 day, 2 day, 3 day}'::INTERVAL[])

0 years 0 mons 6 days 0 hours 0 mins 0.00 secs`

array_sum('{1,2,3}'::TEXT[])

ERROR!

Upvotes: 1

Luis Felipe Vidal
Luis Felipe Vidal

Reputation: 11

In several ways to add up the values ​​of the array, the form I always use is:

WITH X AS(
    SELECT unnest(ARRAY[1, 5, 0, 12, 1, 0, 30, 20, 8, 3, 15, 15, 20, 8]) AS VALOR
)
SELECT SUM(VALOR) FROM X

Result:

138

For more information https://www.postgresql.org/docs/9.1/queries-with.html

Upvotes: 1

jbryanscott
jbryanscott

Reputation: 418

This generalization and reformatting Dmitry's answer helps me understand how it works:

SELECT
  sum(a) AS total
FROM
  (
    SELECT
      unnest(array [1,2,3]) AS a
  ) AS b

Result:

total
 6

Upvotes: 9

Dmitry Seleznev
Dmitry Seleznev

Reputation: 965

SELECT id, (SELECT SUM(s) FROM UNNEST(monthly_usage) s) as total_usage from users;

Upvotes: 54

Related Questions