How to compute the sum of multiple columns in PostgreSQL
25
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
asked Mar 30 '13 at 14:22
PsychePsyche
2,987135780
add a comment |
25
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
asked Mar 30 '13 at 14:22
PsychePsyche
2,987135780
1
You mean a sum per row, or a total sum for all rows?
– Erwin Brandstetter
Mar 30 '13 at 15:38
I mean a sum per row.
– Psyche
Mar 30 '13 at 16:29
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 1:48
add a comment |
25
25
25
6
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
asked Mar 30 '13 at 14:22
PsychePsyche
2,987135780
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
postgresql
asked Mar 30 '13 at 14:22
PsychePsyche
2,987135780
asked Mar 30 '13 at 14:22
PsychePsyche
2,987135780
asked Mar 30 '13 at 14:22
PsychePsyche
2,987135780
asked Mar 30 '13 at 14:22
PsychePsyche
2,987135780
asked Mar 30 '13 at 14:22
PsychePsyche
2,987135780
2,987135780
1
You mean a sum per row, or a total sum for all rows?
– Erwin Brandstetter
Mar 30 '13 at 15:38
I mean a sum per row.
– Psyche
Mar 30 '13 at 16:29
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 1:48
add a comment |
1
You mean a sum per row, or a total sum for all rows?
– Erwin Brandstetter
Mar 30 '13 at 15:38
I mean a sum per row.
– Psyche
Mar 30 '13 at 16:29
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 1:48
1
1
You mean a sum per row, or a total sum for all rows?
– Erwin Brandstetter
Mar 30 '13 at 15:38
You mean a sum per row, or a total sum for all rows?
– Erwin Brandstetter
Mar 30 '13 at 15:38
I mean a sum per row.
– Psyche
Mar 30 '13 at 16:29
I mean a sum per row.
– Psyche
Mar 30 '13 at 16:29
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 1:48
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 1:48
add a comment |
3 Answers
3
active
oldest
votes
42
SELECT COALESCE(col1,0) + COALESCE(col2,0)
FROM yourtable
answered Mar 30 '13 at 14:31
qqxqqx
13.5k24360
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
1
It works with COALESCE(), thank you!
– Psyche
Mar 31 '13 at 14:08
how do I do that with temporary columns that I requested with "AS" ?
– DataGreed
Feb 3 '14 at 20:03
add a comment |
38
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 FreyDaniel Frey
46.2k888144
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 4:53
add a comment |
0
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
AndrewAndrew
6,92944067
add a comment |
Your Answer
StackExchange.ifUsing("editor", function ()
StackExchange.using("externalEditor", function ()
StackExchange.using("snippets", function ()
StackExchange.snippets.init();
);
);
, "code-snippets");
StackExchange.ready(function()
var channelOptions =
tags: "".split(" "),
id: "1"
;
initTagRenderer("".split(" "), "".split(" "), channelOptions);
StackExchange.using("externalEditor", function()
// Have to fire editor after snippets, if snippets enabled
if (StackExchange.settings.snippets.snippetsEnabled)
StackExchange.using("snippets", function()
createEditor();
);
else
createEditor();
);
function createEditor()
StackExchange.prepareEditor(
heartbeatType: 'answer',
autoActivateHeartbeat: false,
convertImagesToLinks: true,
noModals: true,
showLowRepImageUploadWarning: true,
reputationToPostImages: 10,
bindNavPrevention: true,
postfix: "",
imageUploader:
brandingHtml: "Powered by u003ca class="icon-imgur-white" href="https://imgur.com/"u003eu003c/au003e",
contentPolicyHtml: "User contributions licensed under u003ca href="https://creativecommons.org/licenses/by-sa/3.0/"u003ecc by-sa 3.0 with attribution requiredu003c/au003e u003ca href="https://stackoverflow.com/legal/content-policy"u003e(content policy)u003c/au003e",
allowUrls: true
,
onDemand: true,
discardSelector: ".discard-answer"
,immediatelyShowMarkdownHelp:true
);
);
draft saved
draft discarded
Sign up or log in
StackExchange.ready(function ()
StackExchange.helpers.onClickDraftSave('#login-link');
);
Sign up using Google
Sign up using Facebook
Sign up using Email and Password
Post as a guest
Required, but never shown
StackExchange.ready(
function ()
StackExchange.openid.initPostLogin('.new-post-login', 'https%3a%2f%2fstackoverflow.com%2fquestions%2f15719557%2fhow-to-compute-the-sum-of-multiple-columns-in-postgresql%23new-answer', 'question_page');
);
Post as a guest
Required, but never shown
3 Answers
3
active
oldest
votes
3 Answers
3
active
oldest
votes
active
oldest
votes
active
oldest
votes
42
SELECT COALESCE(col1,0) + COALESCE(col2,0)
FROM yourtable
answered Mar 30 '13 at 14:31
qqxqqx
13.5k24360
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
1
It works with COALESCE(), thank you!
– Psyche
Mar 31 '13 at 14:08
how do I do that with temporary columns that I requested with "AS" ?
– DataGreed
Feb 3 '14 at 20:03
add a comment |
42
SELECT COALESCE(col1,0) + COALESCE(col2,0)
FROM yourtable
answered Mar 30 '13 at 14:31
qqxqqx
13.5k24360
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
1
It works with COALESCE(), thank you!
– Psyche
Mar 31 '13 at 14:08
how do I do that with temporary columns that I requested with "AS" ?
– DataGreed
Feb 3 '14 at 20:03
add a comment |
42
42
42
SELECT COALESCE(col1,0) + COALESCE(col2,0)
FROM yourtable
answered Mar 30 '13 at 14:31
qqxqqx
13.5k24360
SELECT COALESCE(col1,0) + COALESCE(col2,0)
FROM yourtable
answered Mar 30 '13 at 14:31
qqxqqx
13.5k24360
edited Mar 30 '13 at 19:16
answered Mar 30 '13 at 14:31
qqxqqx
13.5k24360
answered Mar 30 '13 at 14:31
qqxqqx
13.5k24360
answered Mar 30 '13 at 14:31
qqxqqx
13.5k24360
13.5k24360
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
1
It works with COALESCE(), thank you!
– Psyche
Mar 31 '13 at 14:08
how do I do that with temporary columns that I requested with "AS" ?
– DataGreed
Feb 3 '14 at 20:03
add a comment |
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
1
It works with COALESCE(), thank you!
– Psyche
Mar 31 '13 at 14:08
how do I do that with temporary columns that I requested with "AS" ?
– DataGreed
Feb 3 '14 at 20:03
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
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
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
1
1
It works with COALESCE(), thank you!
– Psyche
Mar 31 '13 at 14:08
It works with COALESCE(), thank you!
– Psyche
Mar 31 '13 at 14:08
how do I do that with temporary columns that I requested with "AS" ?
– DataGreed
Feb 3 '14 at 20:03
how do I do that with temporary columns that I requested with "AS" ?
– DataGreed
Feb 3 '14 at 20:03
add a comment |
38
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 FreyDaniel Frey
46.2k888144
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 4:53
add a comment |
38
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 FreyDaniel Frey
46.2k888144
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 4:53
add a comment |
38
38
38
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 FreyDaniel Frey
46.2k888144
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 FreyDaniel Frey
46.2k888144
answered Mar 30 '13 at 14:41
Daniel FreyDaniel Frey
46.2k888144
answered Mar 30 '13 at 14:41
Daniel FreyDaniel Frey
46.2k888144
answered Mar 30 '13 at 14:41
Daniel FreyDaniel Frey
46.2k888144
46.2k888144
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 4:53
add a comment |
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 4:53
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 4:53
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 4:53
add a comment |
0
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
AndrewAndrew
6,92944067
add a comment |
0
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
AndrewAndrew
6,92944067
add a comment |
0
0
0
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
AndrewAndrew
6,92944067
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
AndrewAndrew
6,92944067
answered Nov 13 '18 at 20:18
AndrewAndrew
6,92944067
answered Nov 13 '18 at 20:18
AndrewAndrew
6,92944067
answered Nov 13 '18 at 20:18
AndrewAndrew
6,92944067
6,92944067
add a comment |
add a comment |
draft saved
draft discarded
Thanks for contributing an answer to Stack Overflow!
- Please be sure to answer the question. Provide details and share your research!
But avoid …
- Asking for help, clarification, or responding to other answers.
- Making statements based on opinion; back them up with references or personal experience.
To learn more, see our tips on writing great answers.
draft saved
draft discarded
Sign up or log in
StackExchange.ready(function ()
StackExchange.helpers.onClickDraftSave('#login-link');
);
Sign up using Google
Sign up using Facebook
Sign up using Email and Password
Post as a guest
Required, but never shown
StackExchange.ready(
function ()
StackExchange.openid.initPostLogin('.new-post-login', 'https%3a%2f%2fstackoverflow.com%2fquestions%2f15719557%2fhow-to-compute-the-sum-of-multiple-columns-in-postgresql%23new-answer', 'question_page');
);
Post as a guest
Required, but never shown
Sign up or log in
StackExchange.ready(function ()
StackExchange.helpers.onClickDraftSave('#login-link');
);
Sign up using Google
Sign up using Facebook
Sign up using Email and Password
Post as a guest
Required, but never shown
Sign up or log in
StackExchange.ready(function ()
StackExchange.helpers.onClickDraftSave('#login-link');
);
Sign up using Google
Sign up using Facebook
Sign up using Email and Password
Post as a guest
Required, but never shown
Sign up or log in
StackExchange.ready(function ()
StackExchange.helpers.onClickDraftSave('#login-link');
);
Sign up using Google
Sign up using Facebook
Sign up using Email and Password
Sign up using Google
Sign up using Facebook
Sign up using Email and Password
Post as a guest
Required, but never shown
Required, but never shown
Required, but never shown
Required, but never shown
Required, but never shown
Required, but never shown
Required, but never shown
Required, but never shown
Required, but never shown
1
You mean a sum per row, or a total sum for all rows?
– Erwin Brandstetter
Mar 30 '13 at 15:38
I mean a sum per row.
– Psyche
Mar 30 '13 at 16:29
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 1:48