sum 求和问题
有如下视图： jhview (goodsID,goodsNAME,goodsSL,riqi)
记录有如下：
goodsID goodsNAME goodsSL riqi
1 钢笔 20 2004-12-15
1 钢笔 20 2005-12-05
2 笔记本 30 2005-12-15
3 铅笔 20 2005-12-20
1 钢笔 10 2005-12-25
现在想计算sum(goodsSL),且过滤goodsID 的重复项
即得出sum(goodsNAME)=20+20+10
goodsID goodsNAME goodsSL
1 钢笔 50
2 笔记本 30
3 铅笔 20
请帮忙写出语句 谢谢!
[解决办法]
select goodsID, goodsNAME, sum(goodsSL) from jhview group by goodsID, goodsNAME
[解决办法]
create table jhview(goodsID int,goodsName nvarchar(100),goodSL int,riqi datetime)
insert into jhview
select 1, '钢笔 ',20, '2004-12-15 ' union all
select 1, '钢笔 ',20, '2005-12-05 ' union all
select 2, '笔记本 ', 30 , ' 2005-12-15 ' union all
select 3, '铅笔 ', 20 , ' 2005-12-20 ' union all
select 1 , '钢笔 ', 10 , ' 2005-12-25 '
select goodsID,goodsname,goodsSL = sum(goodSL)
from jhview
group by goodsID,goodsname
order by goodsID
/*
1钢笔50
2笔记本 30
3铅笔20
*/
[解决办法]
select goodsID, MAX(goodsNAME) as goodsName, sum(goodsSL)
from jhview group by goodsID
