Sum of column in the Postgresql

Question

I am going to calculate the sum of values in specific column. this is my code:

SELECT sum(A.number_Of_Households)
FROM (SELECT 
  
  json_data->'benefits' ->> 'numberOfHouseholds' as number_Of_Households
  
FROM ccbc_public.application a 
    inner join ccbc_public.application_form_data afd on a.id = afd.application_id
    inner join ccbc_public.form_data f on f.id = afd.form_data_id
    where ccbc_public.application_status(a) <> 'draft') As A

When I run the code, I got this error:

ERROR: function sum(text) does not exist Hint: No function matches the given name and argument types. You might need to add explicit type casts.

AS you see in code, I extract the number_Of_Households column by splitting the data. Now I have a number_Of_Households column with number. — Ali Fathalian, Nov 28 '22 at 20:36
[As documented in the manual](https://www.postgresql.org/docs/current/functions-json.html) the `->>` operator returns `text`. So that's not a number. But the error message tells you what you need to do: "*You might need to add explicit type casts*" — , Nov 28 '22 at 20:37
@a_horse_with_no_name you're right. I have a one column with Jason array, and I split it to different columns. one of columns is number_Of_Households that include the numbers. — Ali Fathalian, Nov 28 '22 at 20:44
Does this answer your question? [Postgres: how to excecute the query for Sum as it is giving error?](https://stackoverflow.com/questions/60862461/postgres-how-to-excecute-the-query-for-sum-as-it-is-giving-error) — Luuk, Nov 28 '22 at 20:49
Does this answer your question? [How to convert postgres json to integer](https://stackoverflow.com/questions/20236421/how-to-convert-postgres-json-to-integer) — Bergi, Nov 28 '22 at 21:03

Ferenc Hrutka · Accepted Answer · 2022-11-28T21:42:46.557

Try to cast json_data to integer:

SELECT sum(A.number_Of_Households)
FROM (SELECT (json_data->'benefits' ->> 'numberOfHouseholds')::int as number_Of_Households
    FROM ccbc_public.application a
               inner join ccbc_public.application_form_data afd on a.id = afd.application_id
               inner join ccbc_public.form_data f on f.id = afd.form_data_id
      where ccbc_public.application_status(a) <> 'draft') As A

Sum of column in the Postgresql

1 Answers1