CODEWITHSUNDEEP
sql-serversql-server-2008
Karthik Suvarna
Karthik Suvarna

Reputation: 3

Aggregate may not appear in WHERE clause even if i dont have condition while using SUM

I have this below query

SELECT A.*,SUM(ISNULL(B.DEPTRAN_DEPOSIT,0)-ISNULL(B.DEPTRAN_WITHDRAWAL,0)) as BALANCE
 FROM DEPOSITFDMASTER  A
INNER JOIN DEPOSITTRANSACTION B ON A.DEPSUBTYPE_ID=B.DEPSUBTYPE_ID AND A.FD_ID=B.DEPOSIT_DATAID WHERE SUM(ISNULL(B.DEPTRAN_DEPOSIT,0)-ISNULL(B.DEPTRAN_WITHDRAWAL,0))>10

when i try to execute i will get the below error. If i remove where condtion then the query will work properly.

An aggregate may not appear in the WHERE clause unless it is in a subquery contained in a HAVING clause or a select list, and the column being aggregated is an outer reference.

P.S:DEPTRAN_DEPOSIT and DEPTRAN_WITHDRAWAL both having DataType Decimal(18,2).

Any help appreciated.

Upvotes: 0

Views: 34

Answers (1)

Nighty_
Nighty_

Reputation: 545

I'd suggest you use GROUP BY and HAVING. Something like this

SELECT 
    A.*
    ,SUM(ISNULL(B.DEPTRAN_DEPOSIT,0)-ISNULL(B.DEPTRAN_WITHDRAWAL,0)) as BALANCE
FROM DEPOSITFDMASTER  A
INNER JOIN DEPOSITTRANSACTION B 
ON 
A.DEPSUBTYPE_ID=B.DEPSUBTYPE_ID 
AND     
A.FD_ID=B.DEPOSIT_DATAID 
GROUP BY [All columns in A] --like that A.Column1,A.COlumn2,....,A.ColumnN
HAVING SUM(ISNULL(B.DEPTRAN_DEPOSIT,0)-ISNULL(B.DEPTRAN_WITHDRAWAL,0))>10

Upvotes: 1

Related Questions