求一条ＳＱＬ语句，急！
data1       data2         data3       data4         data4_SUM
  A               1 2 3       7
  A 2 1 1 7
  A 1 2 3 7

  B 1 1 1 3
  B 2 2 2 3

  C 3 3 3 3

  D 4 4 4 9
  D 5 5 5 9

要写一张报表的查询语句，data1中有不同的组，
data4_SUM是根据不同的组合计data４的数据．
表中建立的字段只有data1,data2,data3,data4，
现在要查询的结果如上图所示．请问ＳＱＬ语句应该怎么写？




------解决方案--------------------
create table 表(data1 varchar(10),data2 int,data3 int,data4 int)
insert into 表
select 'A ',1,2,3
union all select 'A ',2,1,1
union all select 'A ',1,2,3
union all select 'B ',1,1,1
union all select 'B ',2,2,2
union all select 'C ',3,3,3
union all select 'D ',4,4,4
union all select 'D ',5,5,5
select *,(select sum(data4) from 表 t where t.data1=表.data1) as data4_SUM
from 表
------解决方案--------------------
select data1,data2,data3,data4,(select sum(data4) from t t1 where t1.data1=t.data1) as data4_sum from t;
------解决方案--------------------
select a.* , b.data4_sum from tb a
left join
(
select data1 , sum(data4) as data4_sum from tb group by data1
) b
on a.data1 = b.data1
------解决方案--------------------
Select date1,date2,date3,date4,date4sum=(Select sum(date4) From tb where date1=A.date1 Group By date4) From tb A