简单查询 - join / sum / group by - Northwind数据库 [英] Simple query - join/sum/group by - Northwind database
本文介绍了简单查询 - join / sum / group by - Northwind数据库的处理方法,对大家解决问题具有一定的参考价值,需要的朋友们下面随着小编来一起学习吧!
问题描述
我学习SQL,使用Northwind示例数据库而且我完全停留在非常简单的问题上:
我想列出一列中的所有产品名称以及第二列中每种产品的总销售量。
首先,我这样做了:
选择 p.ProductId, sum(od.Quantity) as TotalQuantity 来自
产品p 加入 [订单详细信息] od on p.ProductId = od.Productid
group by p.ProductId
,没关系 - 我有产品ID和总数量,但是当我尝试这样的事情时:
选择 p.productid,p.ProductName,sum(od.quantity) as TotalQuantity 来自
产品p join [订单详细信息] od on p.productid = od.productid
group by p.productid
我收到错误:
消息 8120 ,等级 16 ,状态 1 ,行< span class =code-digit> 1
列 ' products.ProductName' 无效 选择列表,因为 不包含 aggregate function 或 GROUP BY 子句。
解决方案
好的,我已经知道了答案:
当您使用聚合函数(如sum)时,您必须按选择中的每个其他字段进行分组
选择 p.productid,p.ProductName,sum(od.quantity) as TotalQuantity 来自
产品p join [订单详细信息] od on p.productid = od.productid
group by p.productid,p .ProductName
I learning SQL, using Northwind example database and I'm completely stuck on very simple problem:
I want to list all products name in one column and total quantities sold for each product in second column.
First, I did this:
select p.ProductId, sum(od.Quantity) as TotalQuantity from
Products p join [Order Details] od on p.ProductId=od.Productid
group by p.ProductId
and that's ok - I have product id's and total quantities, but when I try something like this:
select p.productid, p.ProductName, sum(od.quantity) as TotalQuantity from
products p join [Order details] od on p.productid=od.productid
group by p.productid
I get error:
Msg 8120, Level 16, State 1, Line 1
Column 'products.ProductName' is invalid in the select list because it is not contained in either an aggregate function or the GROUP BY clause.
解决方案
ok, I already know the answer:
When you use aggregate functions (like sum) you have to group by every other field in select
select p.productid, p.ProductName, sum(od.quantity) as TotalQuantity from products p join [Order details] od on p.productid=od.productid group by p.productid, p.ProductName
这篇关于简单查询 - join / sum / group by - Northwind数据库的文章就介绍到这了,希望我们推荐的答案对大家有所帮助,也希望大家多多支持IT屋!
查看全文