[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:

Expand|Select|Wrap|Line Numbers

1. SELECT MBR_NUM, CLAIM_ID, SUM(PAY_AMT) AS SUMOF_PAY_AMT
2. FROM CLAIM
3. WHERE SUMOF_PAY_AMT > 1000.00
4. GROUP BY MBR_NUM, CLAIM_ID
5.  

When I run this, it gives me this error:

Expand|Select|Wrap|Line Numbers

1. 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 :)

Expand|Select|Wrap|Line Numbers

1. SELECT MBR_NUM, CLAIM_ID, SUM(PAY_AMT)
2. FROM CLAIM
3. WHERE ACCT_NUM='3001416'
4. GROUP BY MBR_NUM, CLAIM_ID
5. HAVING SUM(PAY_AMT) > 1000
6.  

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