Cannot perform an aggregate function on an express

Please start any new threads on our new site at https://forums.sqlteam.com. We've got lots of great SQL Server experts to answer whatever question you can come up with.

All Forums

General SQL Server Forums

New to SQL Server Programming

Cannot perform an aggregate function on an express

Author

Topic

slihp
Yak Posting Veteran

61 Posts

Posted - 2007-10-29 : 07:06:22

as a follow on from my last post, here is my sql to give the sum of all payments we have recived for a particular batch of data , dependant upon the rate.

Select
SUM(Case dp.ReceivedByID
When 2 Then dp.PaymentAmount * (cn.DirectRate / 100)
When 1 Then dp.PaymentAmount * (cn.FeeRate / 100)
Else dp.PaymentAmount * ((cn.FeeRate + cn.FieldRate) / 100)

End)
From
dbo.DebtPayment dp,
dbo.ImportBatchItem bi,
dbo.Debt d,
dbo.Contract cn
where
cn.ClientContractID=d.ContractID
AND d.Debtid=dp.DebtID
AND dp.DebtID=bi.ItemID
AND bi.ImportBatchID=101
AND cn.ClientID=1021

cn.DirectRate is the current contract rate. This rate can be changed at any time on the system. i have created a table that stores the the rate for a particulat time frame. with the sql to return ruturn the rate for a time period, client & contract like so.

select cfc.ComRate from dbo.mContractFeeChange cfc
where cfc.ClientID=1021
and cfc.contractid=12
and someDate >= cfc.StartDate AND someDate <= cfc.EndDate

when i plum this as a sub-query into my main query as so

Select
SUM(Case dp.ReceivedByID
When 2 Then dp.PaymentAmount * (

(select cfc.ComRate from dbo.mContractFeeChange cfc
where cfc.ClientID=1021 and cfc.contractid=12
and dp.PaymentDate >= cfc.StartDate AND dp.PaymentDate <=cfc.EndDate)/ 100)

When 1 Then dp.PaymentAmount * (cn.FeeRate / 100)
Else dp.PaymentAmount * ((cn.FeeRate + cn.FieldRate) / 100)

End)
From
dbo.DebtPayment dp,
dbo.ImportBatchItem bi,
dbo.Debt d,
dbo.Contract cn
where
cn.ClientContractID=d.ContractID
AND d.Debtid=dp.DebtID
AND dp.DebtID=bi.ItemID
AND bi.ImportBatchID=101
AND cn.ClientID=1021

I get the following error.

"Cannot perform an aggregate function on an expression containing an aggregate or a subquery"

any help most welcome

Kristen
Test

22859 Posts

Posted - 2007-10-29 : 07:32:35

Change your style of FROM / WHERE to use JOINs instead

Add an additional OUTER JOIN to get the cfc.ComRate, where relevant.

Then use that column in your SUM

Kristen

jsmith8858
Dr. Cross Join

7423 Posts

Posted - 2007-10-29 : 08:40:47

see: http://www.sqlteam.com/article/aggregating-correlated-sub-queries

- Jeff
http://weblogs.sqlteam.com/JeffS

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources