SUM function with conditions (>) or (<)
Question posted by: seraieis
(Member)
on
August 13th, 2008 09:14 PM
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!
2
Answers Posted
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.
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
|
|
|
What is Bytes?
We are a network of experts and professionals in IT and software development that help one another with answers to tough questions and share insights.
Get the best answers to your questions from over 196,983 network members.
|
