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.
SELECT id, (SELECT SUM(s) FROM UNNEST(monthly_usage) s) as total_usage from users;
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
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
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!
