PostgreSQL: Sum Array Column

When you store related numeric values in an array—like multiple sales amounts for an employee, or scores in different rounds, or a list of price adjustments—you often need to total them all up. You can’t use SUM() directly on an array column; you need to first explode the array into individual rows, then aggregate them. It’s a common pattern that trips up people new to PostgreSQL arrays.

The key is understanding that UNNEST converts your array into rows, and then you use standard aggregation. Once you see how this works, you’ll use it constantly.

Problem

Array column with sales values: {100, 250, 150} → need to get 500.

Solution: Use UNNEST with SUM

SELECT
    emp_id,
    first_name,
    sales_amounts,
    SUM(amount) as total_sales
FROM employees e,
UNNEST(e.sales_amounts) AS amount
WHERE e.sales_amounts IS NOT NULL
GROUP BY e.emp_id, e.first_name, e.sales_amounts
ORDER BY total_sales DESC
LIMIT 5;

Result:

emp_id | first_name | sales_amounts      | total_sales
-------+------------+--------------------+------------
     1 | James      | {100,250,150}      |        500
     2 | Mary       | {300,200,100}      |        600
     3 | David      | {500,750}          |       1250

How It Works

UNNEST(array_column) converts the array into individual rows. Then SUM() aggregates them.

Process:

  1. UNNEST converts {100, 250, 150} into three rows: 100, 250, 150
  2. SUM() adds them: 100 + 250 + 150 = 500
  3. GROUP BY combines results back to one row per employee

Solution 2: Using ARRAY_AGG (Reverse Operation)

If you have individual sale records and want to sum them per employee:

SELECT
    emp_id,
    first_name,
    ARRAY_AGG(sale_amount) as sales_array,
    SUM(sale_amount) as total_sales
FROM sales_records
GROUP BY emp_id, first_name
HAVING SUM(sale_amount) > 500
ORDER BY total_sales DESC;

Alternative: One-Liner with Subquery

SELECT
    emp_id,
    first_name,
    (SELECT SUM(amount) FROM UNNEST(sales_amounts) amount) as total_sales
FROM employees
WHERE sales_amounts IS NOT NULL
ORDER BY total_sales DESC;

Real-World Example: Project Budgets

Sum budget array per department:

SELECT
    dept_name,
    project_budgets,
    SUM(budget) as total_budget
FROM (
    SELECT
        d.dept_name,
        ARRAY_AGG(p.budget) as project_budgets,
        p.budget
    FROM departments d
    LEFT JOIN projects p ON d.dept_id = p.dept_id
    GROUP BY d.dept_id, d.dept_name
) AS subquery
WHERE project_budgets IS NOT NULL
GROUP BY dept_name, project_budgets;

Performance Considerations

  • UNNEST: Fast for small arrays (< 100 elements)
  • Index: Add index on array column if frequently queried
  • NULL handling: UNNEST skips NULL arrays; use WHERE IS NOT NULL

Common Variations

-- Sum only non-zero values
SELECT SUM(amount) FROM UNNEST(array_col) AS amount WHERE amount > 0

-- Average of array elements
SELECT AVG(amount) FROM UNNEST(array_col) AS amount

-- Max/Min of array elements
SELECT MAX(amount) FROM UNNEST(array_col) AS amount

-- Count array elements
SELECT COUNT(*) FROM UNNEST(array_col) AS amount

FAQ

Q: How do I sum multiple columns that are arrays? A: Use multiple UNNEST calls with JOIN.

Q: What if the array contains NULL values? A: UNNEST includes NULLs; use WHERE to filter them.

Q: Is there a function to sum without UNNEST? A: No, UNNEST is the standard approach in PostgreSQL.