CODEWITHSUNDEEP
sqlsql-serversql-server-2008
Manish Gupta
Manish Gupta

Reputation: 51

Getting an error with SQL Query Aggregation

I am getting this error:

Cannot perform an aggregate function on an expression containing an aggregate or a sub-query

Below is part of my SQL query_

SUM((CASE 
        WHEN (SELECT [Amount] FROM [Transaction_table] 
              WHERE [Receipt No_] = se.[Receipt No_]) IS NULL
           THEN 1 
           ELSE [Amount] 
     END)) 'Total Amount'

Upvotes: 0

Views: 40

Answers (1)

Yogesh Sharma
Yogesh Sharma

Reputation: 50163

I think ANSI sql standard coalesce() will do that you want

select sum(coalesce([Amount],1)) [Total Amount] from [Transaction_table]
where [Receipt No_]=se.[Receipt No_]

other way by case expression will do the same thing 

select sum(case when [Amount] is null then 1 else [Amount] end) [Total Amount]
from [Transaction_table]
where [Receipt  No_] = se.[Receipt No_];

Upvotes: 1

Related Questions