SQLTeam.com | Weblogs | Forums

Summarizing Data Returned by a Join Query


#1

I have a table (edw_sbx_bt.jjenkins_OVERAGE2) of 40,000 MA_NUM. I want to find occurrences of those specific MA_NUM in another table (edw_ar_fl.ARTBASE) with specific attributes (CONTRACT_NUM = '593' and TRANSACT_STATUS_CD = 'D'). When I run the query below it returns 4 million rows. How can I write a query that will return the 40,000 MA_NUM (in edw_sbx_bt.jjenkins_OVERAGE2) with the sum total of MA_PAID_AMT associated with each?

SELECT edw_ar_fl.ARTBASE.MA_NUM,
edw_ar_fl.ARTBASE.MA_PAID_AMT
FROM edw_ar_fl.ARTBASE
JOIN edw_sbx_bt.jjenkins_OVERAGE2
ON edw_ar_fl.ARTBASE.MA_NUM = edw_sbx_bt.jjenkins_OVERAGE2.MA_NUM
WHERE edw_ar_fl.ARTBASE.CONTRACT_NUM = '593'
AND edw_ar_fl.ARTBASE.TRANSACT_STATUS_CD = 'D'