SELECT语句如何用一条语句实现在对部分列SUM的基础上再对其它列进行AVG

[adwork]

示例如下，有一个表，主键有三列，对其中一列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就成。

各位有没有更好的方法？

[crazyflower]

select id_1, sum(the_sum)/count(id_1) from u_team group by id_1

[marco08]

--try

select id_1, the_sum=sum(the_sum)/count(distinct id_1+id_avg)
from u_team
group by id_1

[rookie_one]

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

[adwork]

应是select id_1, sum(the_sum)/count(distinct id_avg) from u_team group by id_1
但这样要求每个id_1必须包含相同的id_avg个数，个数不同结果就错误了。

[adwork]

TO：coolingpipe(冷箫轻笛)
我需要在前台用，不能使用这么复杂的语法，这样还不如双SELECT语句来得好。