SUM function with conditions (>) or (<)

[seraieis]

Hello everyone!

I've run into a little snag with SQL (which I am still very new at). What I'm trying to do it run a query again medical claim information and return only claims where the total is over $1,000. There can be multiple lines per claim

i.e.:
CLAIM_ID, PAY_AMT
1, 1000
2, 50
2, 50
3, 900
3, 100

Here is the query:

SELECT MBR_NUM, CLAIM_ID, SUM(PAY_AMT) AS SUMOF_PAY_AMT
FROM CLAIM
WHERE SUMOF_PAY_AMT > 1000.00
GROUP BY MBR_NUM, CLAIM_ID
 

When I run this, it gives me this error:

ERROR [42S22][IBM][CLI Driver][DB2] SQL0206N "SUMOF_PAY_AMT" is not valid in the context where it is used. SQLSTATE=42703

Any ideas as to what I'm doing wrong?

Thanks!

[seraieis]

It's truely gratifying to solve your own problem :)

SELECT MBR_NUM, CLAIM_ID, SUM(PAY_AMT)
FROM CLAIM
WHERE ACCT_NUM='3001416'
GROUP BY MBR_NUM, CLAIM_ID
HAVING SUM(PAY_AMT) > 1000
 

I didn't understand anything about the HAVING clause.

[ganeshmn25]

Hi dude,

We need to avoid WHERE Clause while using GROUP BY Clause , Instead we can use HAVING clause that will be act as WHERE clause for checking aggregate functions

Query :
-----------

SELECT MBR_NUM, CLAIM_ID, SUM(PAY_AMT)
FROM CLAIM
GROUP BY MBR_NUM, CLAIM_ID
HAVING SUM(PAY_AMT) > 1000


Regards

Padmanaban.N
DB2 Database Associate