Sum columns per row postgresql

Question

I have table like this:

    CREATE TABLE public."Payments"
(
  user_id integer,
  "1 month later" numeric(19,4),
  "2 months later" numeric(19,4),
  "3 months later" numeric(19,4),
  "4 months later" numeric(19,4),
  "5 months later" numeric(19,4),
  "6 months later" numeric(19,4),
  "7 months later" numeric(19,4),
  "8 months later" numeric(19,4),
  "9 months later" numeric(19,4),
  "10 months later" numeric(19,4),
  "11 months later" numeric(19,4),
  "12 months later" numeric(19,4)

with data inside for exampe like this:

INSERT INTO "Payments" ("user_id", "1 month later", "2 months later", "3 months later", "4 months later", "5 months later", "6 months later", "7 months later", "8 months later", "9 months later", "10 months later", "11 months later", "12 months later") VALUES (134329, 190, 190, 190, NULL, NULL, NULL, NULL, NULL, NULL, NULL, NULL, NULL);

I need to get summ for every user ID, there is a lot of them. Is any function in postgres which can do it swiftly?

First, why are you breaking 'x months later' into different columns. This violates the rules of normal form. What your trying to do is a simple sum. You should google this instead of posting a question here. — user6178746
– user6178746, Commented Apr 15, 2016 at 0:44
I agree with Eric. You should stop and read a good book on relational data. You're going down the wrong path. — Perry Tew
– Perry Tew, Commented Apr 15, 2016 at 2:19
it just a example, I have already this table in access, I need to convert it to psql and wright psql query — Фанки Джаз
– Фанки Джаз, Commented Apr 15, 2016 at 7:47

Community · Accepted Answer · 2017-05-23 12:22:31Z

3

This question is similar to this stackoverflow question. It is a simple use of the SUM aggregate function.

how to group by and return sum row in Postgres

SELECT user_id, SUM("1 month later") as "1 month later",SUM("2 months later") as "2 months later",SUM("3 months later") as "3 months later",SUM("4 months later") as "4 months later",SUM("5 months later") as "5 months later",SUM("6 months later") as "6 months later",SUM("7 months later") as "7 months later",SUM("8 months later") as "8 months later",SUM("9 months later") as "9 months later",SUM("10 months later") as "10 months later",SUM("11 months later") as "11 months later", SUM("12 months later") as "12 months later"
FROM public.payments
GROUP BY user_id

Additionally if the user wants one sum of all 12 columns then simply add the columns.

SELECT user_id, "1 month later" + "2 months later" + "3 months later" + "4 months later" + "5 months later" + "6 months later" + "7 months later" + "8 months later" + "9 months later" + "10 months later" + "11 months later" + "12 months later") as allMonthsLater    FROM public.payments

edited May 23, 2017 at 12:22

CommunityBot

11 silver badge

answered Apr 15, 2016 at 0:49

Daniel Gale

6634 silver badges13 bronze badges

Sign up to request clarification or add additional context in comments.

2 Comments

Patrick Over a year ago

In this case you should make a comment, not an answer with a link to an existing answer. Besides, the link is not the same question, so it is no good.

Daniel Gale Over a year ago

In the case that I provided a code example, I made this an answer. I wanted to contribute but do not have enough points to make a comment (50 needed), however I can make an answer. I maintain that the question is the same but agree that the two examples are different.

Collectives™ on Stack Overflow

Sum columns per row postgresql

1 Answer 1

2 Comments

Your Answer

Linked

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

2 Comments

Your Answer

Sign up or log in

Post as a guest

Linked

Related