jsonb calculate the column sum() and select the highest value / select from (select(sum()))-CodePudding

I need to select the largest value that results from sum()

SELECT
    row_data->>'companyName' AS "NOME EMPRESA", 
    count(row_data->>'companyName') AS "Count"  ,
    sum((row_data->>'bankMovementAmount')::float  0.0) AS "VALOR S"  
 FROM public.teste
 where abbreviation = 'BMO' AND row_data->>'bankMovementOperationType'= 'S'  
GROUP BY row_data->>'companyName'
ORDER BY row_data->>'companyName' ASC;

Does anyone know how to help me, in a select from (select from)

how to filter in cast exit goes into

bankMovementOperationType'= 'S'exit

bankMovementOperationType'= 'E' goes into

sum((row_data->>'bankMovementAmount')::float 0.0) AS "VALOR S"

enter image description here

CodePudding user response：

You can use either a conditional multiplicator

SUM(
  (row_data->>'bankMovementAmount')::float
  * (CASE row_data->>'bankMovementOperationType'
    WHEN 'S' THEN -1.0
    WHEN 'E' THEN  1.0
  END)
) AS "VALOR S"

or two filtering aggregates

( SUM( (row_data->>'bankMovementAmount')::float ) FILTER (WHERE row_data->>'bankMovementOperationType' = 'E')
- SUM( (row_data->>'bankMovementAmount')::float ) FILTER (WHERE row_data->>'bankMovementOperationType' = 'S')
) AS "VALOR S"