Click here to Skip to main content
12,503,829 members (31,691 online)
Rate this:
 
Please Sign up or sign in to vote.
See more: SQL SQL-Server
I have to calculate the average but i keep on getting this error :

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

select fa.new_contractid,AVG(Sum(vews.TransactionCount)) as BillableUsage from BingMapsPlatform_Staging.dbo.VEWSDailyAggTrans vews
inner join BingMapsPlatform_MSCRM.dbo.FilteredAccount fa
on fa.new_accountid=vews.PrimaryId
where fa.new_contractid = '6145412166'
group by fa.new_contractid

How should i modify the script to get the average.
Posted 11-Dec-12 1:55am
Rate this: bad
 
good
Please Sign up or sign in to vote.

Solution 1

Try this

select fa.new_contractid,AVG(Sum(vews.TransactionCount)) as BillableUsage from BingMapsPlatform_Staging.dbo.VEWSDailyAggTrans vews,BingMapsPlatform_MSCRM.dbo.FilteredAccount fa
where fa.new_accountid=vews.PrimaryId
and fa.new_contractid = '6145412166'
group by fa.new_contractid
  Permalink  
Rate this: bad
 
good
Please Sign up or sign in to vote.

Solution 2

Hi Prasant,

try this

select fa.new_contractid,AVG(Summation) as BillableUsage from(select Sum(vews.TransactionCount) as Summation from BingMapsPlatform_Staging.dbo.VEWSDailyAggTrans) vews
inner join BingMapsPlatform_MSCRM.dbo.FilteredAccount fa
on fa.new_accountid = vews.PrimaryId
where fa.new_contractid = '6145412166'
group by fa.new_contractid


Thanks
  Permalink  

This content, along with any associated source code and files, is licensed under The Code Project Open License (CPOL)

  Print Answers RSS
Top Experts
Last 24hrsThis month


Advertise | Privacy | Mobile
Web02 | 2.8.160919.1 | Last Updated 11 Dec 2012
Copyright © CodeProject, 1999-2016
All Rights Reserved. Terms of Service
Layout: fixed | fluid

CodeProject, 503-250 Ferrand Drive Toronto Ontario, M3C 3G8 Canada +1 416-849-8900 x 100