PostgreSQL Question posted in
The official documentation can be found here.

Postgresql – SUM same colum virtual result POSTGRES

SELECT (jsonb_array_elements((balance)::jsonb->'savings')->'amount')::float as virtual_balance FROM accounts WHERE id = 58117;

Result:

virtual_balance
---------------
162.09
0.16

I need to select this result to be added together, resulting in 162.25
the expected result should look like

virtual_balance
---------------
162,25

I needed it to be in the same select without using WHEN or other loops, my database and postgres

virtual_balance
---------------
162,25
Tags:

2

Answers


  1. 0

    Put the set-returning function in a lateral table expression, then you can use SUM:

    SELECT SUM((savings->'amount')::float) AS virtual_balance 
FROM accounts, jsonb_array_elements(balance::jsonb->'savings') savings
WHERE id = 58117;

    or

    SELECT SUM(amount) AS virtual_balance 
FROM accounts, jsonb_to_recordset(balance::jsonb->'savings') savings(amount float)
WHERE id = 58117;
    Login or Signup to reply.
  2. 0

    You can add the decimal separator as a comma, change it to characters (if you need the numeric values you have to convert it back to float later) using to_char:

    https://www.postgresql.org/docs/current/functions-formatting.html

    Query:

    SELECT to_char(SUM((jsonb_array_elements((balance)::jsonb->'savings')->>'amount')::float), 'FM999999999D00') as virtual_balance FROM accounts WHERE id = 58117;

    The reason for using it like this is because of the following:

    The pattern characters S, L, D, and G represent the sign, currency
    symbol, decimal point, and thousands separator characters defined by
    the current locale (see lc_monetary and lc_numeric). The pattern
    characters period and comma represent those exact characters, with the
    meanings of decimal point and thousands separator, regardless of
    locale.

    So D is the comma instead of period (.),

    Tested on PosgreSQL 16

    Login or Signup to reply.
Please signup or login to give your own answer.

Back To Top
Search