calculate sum of two columns in PostgreSQL 9.5

Question

I have a table of posts, each with an insight jsonb column containing something like the data sample below.

Data sample (comes from Facebook, so cannot change format)

[
  {
    "name": "post_story_adds_unique",
    "values": [
      { "value": 93 }
    ],
  },
  {
    "name": "post_story_adds",
    "values": [
      {  "value": 100 }
    ]
  },
  {
    "name": "post_impressions_organic_unique",
    "values": [
      {  "value": 123 }
    ]
  },
  ...
]

I want to have a calculated sum of reach and viral and then have it ordered by the total.

Desired Results

id  message                       post_created          reach   viral   total
69  This World Family dablah...   2016-05-11 18:44:16   6683    646     7329
...

I managed to get the results so far but I cannot figure how to get the sum of the two columns. I don't know where to add another join or select to sum the two columns.

Results so far

id  message                       post_created          reach   viral
69  This World Family dablah...   2016-05-11 18:44:16   6683    646
58  blah blah flip flop blah...   2016-05-22 11:00:01   4880    403
55  This is another message ...   2016-05-24 10:00:00   4417    109

I've tried various ways such as including the SUM (reach + viral) as total in the first SELECT but mostly I get back errors saying columns don't exist.

Heres my SQL so far:

SELECT
    id,
    message,
    post_created,
    obj.value->'values'->0->'value' AS reach,
    obj2.value->'values'->0->'value' AS viral
  FROM (
    SELECT
      id,
      message,
      post_created,
      insights
    FROM posts
    WHERE (
      page_id = 4 AND
      post_created >= '2016-05-01 00:00:00' AND
      post_created <= '2016-05-31 23:59:59' AND
      insights @> '[{"name":"post_impressions_organic_unique"}, {"name":"post_impressions_viral_unique"}]'
    )
  ) e1
  JOIN LATERAL jsonb_array_elements(insights) obj(value) ON obj.value->>'name' = 'post_impressions_organic_unique'
  JOIN LATERAL jsonb_array_elements(insights) obj2(value) ON obj2.value->>'name' = 'post_impressions_viral_unique'
  ORDER BY reach DESC;

Omar Qureshi · Accepted Answer · 2016-06-15 16:14:24Z

1

Unsure if jsonb will have any impact, but, adding the values of two columns is as simple as using +

create table foo(a integer,b integer);
insert into foo values (3,4);
select *, a+b as total from foo;

 a | b | total 
---+---+-------
 3 | 4 |     7
(1 row)

answered Jun 15, 2016 at 16:14

Omar Qureshi

9,1033 gold badges35 silver badges35 bronze badges

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

9 Comments

user115014 Over a year ago

Thanks, I tried that but in my case I get back column "reach" does not exist

Omar Qureshi Over a year ago

have you tried obj.value->'values'->0->'value' + obj2.value->'values'->0->'value' ?

user115014 Over a year ago

yep - I get this error: ERROR: operator does not exist: unknown + jsonb LINE 7: obj.value->'values'->0->'value' + obj2.value->'values'->0->... ^ HINT: No operator matches the given name and argument type(s). You might need to add explicit type casts.

user115014 Over a year ago

even obj + obj2 or obj.value + obj2.value doesn't work, get same error

Omar Qureshi Over a year ago

Right, so it's a JSONB problem, try casting with ::integer

|

Pierre Vivet · Accepted Answer · 2016-06-15 16:10:27Z

0

To do that you need to update your table like so :

UPDATE yourTable SET resultsRow = firtRow + secondRow;

answered Jun 15, 2016 at 16:10

Pierre Vivet

1338 bronze badges

1 Comment

user115014 Over a year ago

Thanks but I don't want it saving to the table , I want to just run the query.

Collectives™ on Stack Overflow

calculate sum of two columns in PostgreSQL 9.5

2 Answers 2

9 Comments

1 Comment

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

2 Answers 2

9 Comments

1 Comment

Your Answer

Sign up or log in

Post as a guest

Related