SELECT语句如何用一条语句实现在对部分列SUM的基础上再对其它列进行AVG
示例如下，有一个表，主键有三列，对其中一列SUM，一列AVG，一列显示：
CREATE   TABLE   [dbo].[u_team]   (
[id_1]   varchar(16)   NOT   NULL,
[id_avg]   varchar(16)   NOT   NULL,
[id_sum]   varchar(16)   NOT   NULL,

[the_sum]   [numeric](18,2)   NULL,
PRIMARY   KEY   (id_1,   id_avg,   id_sum)
)   ON   [PRIMARY]
GO

正常需要这样实现
SELECT   id_1,   AVG(the_sum)   AS   the_sum
FROM   (SELECT   id_1,   id_avg,   SUM(the_sum)   AS   the_sum   FROM   u_team
GROUP   BY   id_1,   id_avg)   v_temp
GROUP   BY   id_1

但因为多次用到这类查询语句，所以最好是能只用一次SELECT的语句来实现（建视图更不可取），列的语法复杂无所谓，只要不需两次SELECT就成。

各位有没有更好的方法？


------解决方案--------------------
select id_1, sum(the_sum)/count(id_1) from u_team group by id_1
------解决方案--------------------
--try

select id_1, the_sum=sum(the_sum)/count(distinct id_1+id_avg)
from u_team
group by id_1
------解决方案--------------------
select id_1, sum(the_sum)/count(id_avg) from u_team group by id_1

------解决方案--------------------
declare @table table
(
id int,
id_1 int,
i_avg int,
i_sum int
)

insert into @table select 1,1,1,14
insert into @table select 1,1,1,14
insert into @table select 2,1,2,15
insert into @table select 2,1,2,15
insert into @table select 3,1,3,16
insert into @table select 3,1,3,16
insert into @table select 4,2,1,17
insert into @table select 5,2,2,18
insert into @table select 6,2,3,19
insert into @table select 7,3,1,10
insert into @table select 8,3,2,11
insert into @table select 9,3,3,12
insert into @table select 10,4,1,13
insert into @table select 11,4,2,100

--楼主的语句
select id_1,avg(i_sum)
from
(select id_1,i_avg,sum(i_sum) as i_sum from @table group by id_1,i_avg
)tt
group by id_1

--结果
1 30
2 18
3 11
4 56

--更改后的
select id_1,sum(i_sum) / count(distinct i_avg) as i_sum
from @table
group by id_1

--结果
1 30
2 18
3 11
4 56