CODEWITHSUNDEEP
sql
S Peabody
S Peabody

Reputation: 1

SQL Query trouble filtering out Sums

I need help figuring out how to refine the following SQL query from an Oracle Database:

SELECT DISTINCT 
    PR.DOC_ID, PR.AMEND_NO, LEDGER.ACCT_ID, APPRVL_CHAIN.APPRVR, 
    SUM(LEDGER.COMMIT)
FROM 
    LUFSMGR.APPRVL_CHAIN APPRVL_CHAIN, 
    LUFSMGR.LEDGER LEDGER, 
    LUFSMGR.PR PR
WHERE 
    PR.DOC_ID = LEDGER.DOC_ID 
    AND LEDGER.DOC_ID = APPRVL_CHAIN.DOC_ID 
    AND APPRVL_CHAIN.AMEND_NO = LEDGER.AMEND_NO 
    AND LEDGER.AMEND_NO = PR.AMEND_NO 
    AND ((PR.DOC_ID Like '2116%') 
         AND (LEDGER.ACCT_ID Like '25601 105300SD%') 
         AND (APPRVL_CHAIN.APPRVR_ROLE='Funds Manager'))
GROUP BY 
    PR.DOC_ID, PR.AMEND_NO, LEDGER.ACCT_ID, APPRVL_CHAIN.APPRVR
HAVING 
    (SUM(LEDGER.COMMIT) <> 0)
ORDER BY 
    PR.DOC_ID

This results in data whose sample looks like this:

Sample Data

I need to filter out all Doc_ID where the sum of commitments including amendments is = 0. For example Doc_ID 21162465SD541 should be filtered out since the sum of Ammend_No 0,1,and 2 are 0.

Upvotes: 0

Views: 38

Answers (1)

Gordon Linoff
Gordon Linoff

Reputation: 1269693

If you want information at the document level, then only include that in the GROUP BY:

SELECT PR.DOC_ID, Sum(LEDGER.COMMIT)
FROM LUFSMGR.APPRVL_CHAIN APPRVL_CHAIN JOIN
     LUFSMGR.LEDGER LEDGER
     ON LEDGER.DOC_ID = APPRVL_CHAIN.DOC_ID AND APPRVL_CHAIN.AMEND_NO = LEDGER.AMEND_NO JOIN
     LUFSMGR.PR PR
     ON PR.DOC_ID = LEDGER.DOC_ID AND  AND LEDGER.AMEND_NO = PR.AMEND_NO 
WHERE (PR.DOC_ID Like '2116%') AND
      (LEDGER.ACCT_ID Like '25601 105300SD%') AND 
      (APPRVL_CHAIN.APPRVR_ROLE = 'Funds Manager')
GROUP BY PR.DOC_ID
HAVING Sum(LEDGER.COMMIT) <> 0
ORDER BY PR.DOC_ID;

Notes:

  • Learn to use proper JOIN syntax. Simple rule: Never use commas in the FROM clause.
  • Using SELECT DISTINCT with GROUP BY is almost never the right thing to do.

Upvotes: 2

Related Questions