Filter Select according to NULL values

Question

removing row on the basis of function output in postgresql :

in following query i am finding the sum of settlement value having value less then zero

   select order_id , order_item_id , 
          case when sum(settlement_value) < 0  then
                     sum(settlement_value) 
            end
  from 
       "Recon".fk_courier_return 
 group by 
        order_item_id, order_id

output is :

 Order id               Order_item_id  case

"OD101176788313080800";"115303430";
"OD40814018539";       "91216839";   -76.31
"OD40627030417";        "80207748";
"OD40913016810";       "98742811";
 "OD40701060277";       "80945680";
"OD100995986740582701";"108843075";  -42.07
"OD102293318278386300";"160498343";
 "OD40825127281";      "94066239";   -78.59
"OD200943992437302100";"106376239";
"OD40811280064";       "90512463";
"OD102056004796760300";"150562685";
 "OD40705090946";      "81791269";
"OD100996265730486401";"108855460";
"OD301050807164753201";"111119783";
"OD000825719620275400";" 100799584";
"OD001027651714425302"; "110064133";
"OD102181807536975500"; "156169384";
"OD000790021256135600";"99916743";
"OD101801498273347001";"139754487";
"OD40825034475";       "93908112";     -78.59

now i want to remove all row that are not having value in case column

what changes should i do in my query

Answer 1

To answer your question (not showing rows on the basis of function output) you could rewrite your query this way:

SELECT
  order_id,
  order_item_id,
  sum_settlement_value
FROM (
  SELECT order_id, order_item_id, 
         CASE WHEN sum(settlement_value) < 0  then
                   sum(settlement_value) 
         END AS sum_settlement_value
  FROM 
    "Recon".fk_courier_return 
   GROUP BY 
     order_item_id, order_id
) s
WHERE
  sum_settlement_value IS NOT NULL

(this could be useful if your function is too complicated) but if you just don't want to show rows not having a negative value, it is better to use HAVING clause:

SELECT
  order_id,
  order_item_id,
  SUM(settlement_value)
FROM 
   "Recon".fk_courier_return 
GROUP BY 
  order_item_id,
  order_id
HAVING
  SUM(settlement_value) < 0