在计算Transact-sql中的平均值时出错 [英] Getting error while calculating the average in Transact-sql

问题描述

我必须计算平均值，但我继续收到此错误：



无法对包含聚合或子查询的表达式执行聚合函数。 />

 选择 fa.new_contractid，AVG（Sum（vews.TransactionCount））  as  BillableUsage 来自 BingMapsPlatform_Staging.dbo.VEWSDailyAggTrans vews 
 内部 加入 BingMapsPlatform_MSCRM.dbo.FilteredAccount fa 
  on  fa.new_accountid = vews.PrimaryId 
 其中 fa.new_contractid = '  6145412166' 
  group   by  fa.new_contractid 

如何修改脚本以获得平均值。

 选择 fa.new_contractid，AVG（Sum（vews.TransactionCount）） as  BillableUsage 来自 BingMapsPlatform_Staging.dbo .VEWSDailyAggTrans vews，BingMapsPlatform_MSCRM.dbo.FilteredAccount fa 
 其中 fa.new_accountid = vews.PrimaryId 
 和 fa.new_contractid = '  6145412166' 
  group    fa.new_contractid 

Hi Prasant，



试试这个

 选择 fa.new_contractid，AVG（求和）作为 BillableUsage 来自（选择 Sum（vews.TransactionCount） as 求和来自 BingMapsPlatform_Staging.dbo.VEWSDailyAggTrans）vews 
  inner   join  BingMapsPlatform_MSCRM.dbo.FilteredAccount fa 
  on  fa.new_accountid = vews.PrimaryId 
 其中​​ fa.new_contractid = '  6145412166' 
  group   by  fa.new_contractid 

谢谢

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.

解决方案

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

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

这篇关于在计算Transact-sql中的平均值时出错的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！