How to compute the sum of multiple columns in PostgreSQL

Question

I would like to know if there's a way to compute the sum of multiple columns in PostgreSQL.

I have a table with more than 80 columns and I have to write a query that adds each value from each column.

I tried with SUM(col1, col2, col3 etc) but it didn't work.

I just want to clarify further that you did not mean "I want to sum all of column A and then add it to the sum of column B", you meant "In each row, I want to sum the value in column A and the value in column B and store it in the result". — Noumenon, Sep 29 '16 at 01:48

qqx · Accepted Answer · 2013-03-30T19:16:33.710

113

SELECT COALESCE(col1,0) + COALESCE(col2,0)
FROM yourtable

edited Mar 30 '13 at 19:16

answered Mar 30 '13 at 14:31

qqx

18,947
4
64
68

1

I tried that, but there are columns where there are no values (NULL) and the final result is NULL, so its not ok. – Psyche Mar 30 '13 at 16:30
Answer modified to account for that, and the comment that you're looking for a per-row sum. – qqx Mar 30 '13 at 19:17
how do I do that with temporary columns that I requested with "AS" ? – DataGreed Feb 03 '14 at 20:03
@qqx Can someone explain why we need to use COALESCE here if col1 and col2 are both numeric data types? – Bulrush Jan 30 '20 at 11:08
@Bulrush that would be a separate question, and likely one that's already answered. – qqx Jan 30 '20 at 15:53

score 78 · Answer 2 · answered Mar 30 '13 at 14:41

78

It depends on how you'd like to sum the values. If I read your question correctly, you are looking for the second SELECT from this example:

template1=# SELECT * FROM yourtable ;
 a | b 
---+---
 1 | 2
 4 | 5
(2 rows)

template1=# SELECT a + b FROM yourtable ;
 ?column? 
----------
        3
        9
(2 rows)

template1=# SELECT SUM( a ), SUM( b ) FROM yourtable ;
 sum | sum 
-----+-----
   5 |   7
(1 row)

template1=# SELECT SUM( a + b ) FROM yourtable ;
 sum 
-----
  12
(1 row)

template1=#

answered Mar 30 '13 at 14:41

Daniel Frey

55,810
13
122
180

Thanks for the comprehensive answer. Like the comments in the other answer, I was having trouble with SUM returning NULL so here's a fix if anyone needs: `SELECT GREATEST(0, SUM(a + b)) FROM yourtable;` – Lorenzo Jun 29 '16 at 04:53

score 18 · Answer 3 · answered Nov 13 '18 at 20:18

18

Combined the current answers and used this to get total SUM:

SELECT SUM(COALESCE(col1,0) + COALESCE(col2,0)) FROM yourtable;

answered Nov 13 '18 at 20:18

Andrew

18,680
13
103
118

score 0 · Answer 4 · edited Apr 14 '22 at 14:45

0

SELECT(
    SELECT SUM(t.f)
    FROM (VALUES (yourtable.col1), (yourtable.col2), (yourtable.col3)) t(f)
)
FROM yourtable;

edited Apr 14 '22 at 14:45

jrswgtr

2,287
8
23
49

answered Apr 14 '22 at 11:44

Mikhail

1

Can you explain how this works? What would you call `t(f)`? – chrismarx Aug 09 '23 at 17:30

How to compute the sum of multiple columns in PostgreSQL

4 Answers4

Linked