sum 求和问题,再次求解!

[sione88]

有如下视图： jhview (goodsID,goodsNAME,goodsSL,goodsDJ,goodsJE,riqi)

记录有如下：
goodsID goodsNAME goodsSL goodsDJ goodsJE goodsGG riqi
1 钢笔 20 2 40 长城牌 2004-12-15
1 钢笔 20 2 40 长城牌 2005-12-05
2 笔记本 30 1 30 大号 2005-12-15
3 铅笔 20 1 20 长细 2005-12-20
4 钢笔 10 4 40 英雄牌 2005-12-25

现在想计算sum(goodsSL),sum(goodsJE)且过滤goodsID 的重复项
即得出sum(goodsSL),sum(goodsJE),名字相同的物品它们的ID号(goodsID)和规格 (goodsGG)不相同.现在要显示如下视图:
goodsID goodsNAME goodsSL goodsJE goodsGG
1 钢笔 40 80 长城牌
2 笔记本 30 30 大号
3 铅笔 20 20 长细
4 钢笔 10 40 英雄牌

请帮忙写出语句 谢谢!

[mysky0103]

select goodsID , goodsNAME , sum(goodsSL) , sum(goodsJE ) , goodsGG
from jhview group by goodsID

[OracleRoob]

select
goodsID,
goodsNAME,
sum(goodsSL),
sum(goodsJE ),
goodsGG
from jhview
group by goodsID

[OracleRoob]

--改下
select
goodsID,
goodsNAME,
sum(goodsSL),
sum(goodsJE ),
goodsGG
from jhview
group by goodsID,goodsNAME,goodsGG

[OracleRoob]

create table #jhview (goodsID int,goodsNAME varchar(100),goodsSL int,goodsDJ int,goodsJE int,goodsGG varchar(100),riqi datetime)

insert into #jhview select 1,'钢笔',20,2,40,'长城牌','2004-12-15'
insert into #jhview select 1,'钢笔',20,2,40,'长城牌','2005-12-05'
insert into #jhview select 2,'笔记本',30,1,30,'大号','2005-12-15'
insert into #jhview select 3,'铅笔',20,1,20,'长细','2005-12-20'
insert into #jhview select 4,'钢笔',10,4,40,'英雄牌','2005-12-25'

select
goodsID,
goodsNAME,
sum(goodsSL) as goodsSL,
sum(goodsJE ) as goodsJE,
goodsGG
from #jhview
group by goodsID,goodsNAME,goodsGG

drop table #jhview

[mysky0103]

对了又写错了

select goodsID , goodsNAME , sum(goodsSL) , sum(goodsJE ) , goodsGG
from jhview group by goodsID,goodsNAME,goodsGG 应该这样 ,不好意思啊

[pastbee]

这问题散分的吧,我来接分

[fckadxz]

--这种写法有新意哟，既然楼主开了贴，多写个方案讨论一下。
select goodsID , min(goodsNAME),sum(goodsSL), sum(goodsJE ) ,min(goodsGG)
from jhview group by goodsID

[zs621]

select goodsID , goodsNAME , sum(goodsSL) , sum(goodsJE ) , goodsGG
from jhview group by goodsID

[anison]

select goodsID,goodsName,goodsSL=sum(goodsSL),goodsJE=sum(goodsJE),goodsGG from jhview group by goodsID,goodsName,goodsGG

[CodeMoving]

select a.goodsID,b.goodsName,a.sum(goodsSl),a.sum(goodsJE),b.goodsGG from (select goodsID,sum(goodsSl),sum(goodsJE) from jhview ) a left join jhview b on a.goodsID=b.goodsID

[divmedia]

select goodsID , goodsNAME , sum(goodsSL) , sum(goodsJE ) , goodsGG
from jhview group by goodsID

[divmedia]

select
goodsID,
goodsNAME,
sum(goodsSL),
sum(goodsJE ),
goodsGG
from jhview
group by goodsID,goodsNAME,goodsGG