SQL - how to display employees that has sum less than 5000

Question

I have encountered a problem here. in the total_trans_amount column, How do I check the sum of total_trans_amount for each employee and display those employee which has less than 5000 in total. Based on the image below, P0947 should have a total of 5700. I want to remove the three row data that consist of P0947 because it exceeds 5700. Please refer to my current SQL query and image below.

SELECT DISTINCT b
    ,SUM(trans_amount2) AS [total_trans_amount]
    ,adjust_amount
    ,trans_amount2
    ,transaction_order
    ,reimname_en
    ,currency_code
    ,remark
    ,disease
FROM temp_reim_used
WHERE company_code = 'pdc'
    AND reim_code = 'CLNC_NPL'
    AND a = a
    AND b = b
    AND trans_amount != 0
    AND reimbalance_id = reimbalance_id
    AND (
        (created_date >= {d '2017-01-01' }
            AND created_date <= {d '2017-09-23' })
        )
GROUP BY b
    ,reim_code
    ,adjust_amount
    ,trans_amount2
    ,transaction_order
    ,reimname_en
    ,currency_code
    ,remark
    ,disease
HAVING SUM(trans_amount2) > - 6000
ORDER BY b ASC

Answer 1

You can add extra filter like this:

WITH CTE
AS
(
   SELECT b,
     SUM(trans_amount2) as [total_trans_amount]
   FROM temp_reim_used
  where company_code = 'pdc' AND reim_code = 'CLNC_NPL'
  AND a = a AND b = b
  AND trans_amount != 0  AND reimbalance_id = reimbalance_id
  AND ((created_date >= {d '2017-01-01'} AND created_date <= {d '2017-09-23'})) 
   GROUP BY b
)
SELECT 
  b,
  SUM(trans_amount2) as [total_trans_amount], 
  adjust_amount,trans_amount2,transaction_order,
  reimname_en , currency_code, remark, disease
from temp_reim_used
where company_code = 'pdc' AND reim_code = 'CLNC_NPL'
  AND a = a AND b = b
  AND trans_amount != 0  AND reimbalance_id = reimbalance_id
  AND ((created_date >= {d '2017-01-01'} AND created_date <= {d '2017-09-23'})) 
  AND b IN (SELECT b FROM CTE WHERE total_trans_amount >= -5000)
GROUP BY b,reim_code, adjust_amount,trans_amount2,transaction_order, 
reimname_en, currency_code, remark, disease 
having SUM(trans_amount2) >-6000 
order by b asc

Things to note here:

• The CTE will give you the total amount for each b. So these three rows will give you the 5700.
• The condition AND b IN (SELECT b FROM CTE WHERE total_trans_amount >= -5000) will give you only those rows that less than -5000.
• No need for DISTINCT with GROUP BY the way you did.