Question

一个关于SQL中使用group by SUM求和的问题

select top 10 商品编号 = b.goodsId,商品名称 = b.goodsName,销售数量 = sum(c.quantity)
from GoodsType a join GoodsInfo b on a.typeId = b.typeId
join OrderGoodsInfo c on b.goodsId = c.goodsId
where a.typeName = '野营用品'group by c.quantity,b.goodsId,b.goodsName order by c.quantity desc
go

Answer 1

select top 10 商品编号 = b.goodsId,商品名称 = b.goodsName,销售数量 = sum(c.quantity)
from GoodsType a join GoodsInfo b on a.typeId = b.typeId
join OrderGoodsInfo c on b.goodsId = c.goodsId
where a.typeName = '野营用品'group by b.goodsId,b.goodsName order by c.quantity desc
go

追问

会有错误：
消息 8127，级别 16，状态 1，第 4 行
ORDER BY 子句中的列 "OrderGoodsInfo.quantity" 无效，因为该列没有包含在聚合函数或 GROUP BY 子句中。

追答

select top 10 商品编号 = b.goodsId,商品名称 = b.goodsName,销售数量 = sum(c.quantity)
from GoodsType a join GoodsInfo b on a.typeId = b.typeId
join OrderGoodsInfo c on b.goodsId = c.goodsId
where a.typeName = '野营用品'group by b.goodsId,b.goodsName order by sum(c.quantity) desc