How to compute the sum of multiple columns in PostgreSQL
PostgresqlPostgresql Problem Overview
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.
Postgresql Solutions
Solution 1 - Postgresql
SELECT COALESCE(col1,0) + COALESCE(col2,0)
FROM yourtable
Solution 2 - Postgresql
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=#
Solution 3 - Postgresql
Combined the current answers and used this to get total SUM:
SELECT SUM(COALESCE(col1,0) + COALESCE(col2,0)) FROM yourtable;
Solution 4 - Postgresql
SELECT(
SELECT SUM(t.f)
FROM (VALUES (yourtable.col1), (yourtable.col2), (yourtable.col3)) t(f)
)
FROM yourtable;