在sql server 2005的联接表中使用group by [英] using group by in joined tables in sql server 2005

问题描述

我是sql server中的新手
我正在使用sql server 2005和northwind数据库
我想加入订单和订单明细表，并使用group by子句计算每个客户的购买总额.

当我用

I am newbie in sql server
i''m using sql server 2005 and the northwind database
i wanted to join order and order details tables and calculate the total purchase made by each customer by using group by clause.

When i used

select orders.orderid,orders.customerid,orders.orderdate,orders.shipcity,orders.shipcountry,
orderdetails.unitprice,orderdetails.quantity,(orderdetails.unitprice*orderdetails.quantity )as total from orders 
inner join orderdetails on orders.orderid=orderdetails.orderid group by orders.customerid

我收到错误消息，列

i received the error message Column

orders.OrderID

在选择列表中无效，因为它没有包含在聚合函数或GROUP BY子句中.

谁能解释错误消息的原因


作为其他方式，我尝试创建视图，然后使用Group by子句，如下所示

is invalid in the select list because it is not contained in either an aggregate function or the GROUP BY clause.

Can anyone explain the reason for the error message


As other way around i tried to create a view and then use the Group by clause as below

create view Purchase_by_each_customer
as 
select orders.orderid,orders.customerid,orders.orderdate,orders.shipcity,orders.shipcountry,orderdetails.unitprice,orderdetails.quantity,(orderdetails.unitprice*orderdetails.quantity )as total from orders inner join orderdetails on orders.orderid=orderdetails.orderid 


select orderid,customerid,sum(total) from Purchase_by_each_customer group by customerid

错误消息是选择列表中的列``Purchase_by_each_customer.orderid''无效，因为它既不在聚合函数中也不在GROUP BY子句中.

聚合函数可以与视图一起使用吗?
如何在多个表联接中使用分组依据?
请帮我

Error message is Column ''Purchase_by_each_customer.orderid'' is invalid in the select list because it is not contained in either an aggregate function or the GROUP BY clause.

Can aggregate functions be used with views,if so how?
how to use Group by in multiple table joins?
Plz help me

推荐答案

您的解决方案:

您需要更改您的SQL

当我们在查询中使用group by子句时，我们需要包括select语句中的所有字段(不包括诸如sum，min，max等的虚拟列)

Hi,

Your Solution:

You need to change your SQL

when we are using group by clause in our query we need to include all field which are in select statement (excluding virtual columns like sum,min,max etc...)

select 
orders.orderid,orders.customerid,orders.orderdate,orders.shipcity,orders.shipcountry,orderdetails.unitprice,orderdetails.quantity,
	(orderdetails.unitprice*orderdetails.quantity )as total 
from orders 
	inner join orderdetails on orders.orderid=orderdetails.orderid 
group by orders.orderid,orders.customerid,orders.orderdate,orders.shipcity,orders.shipcountry,orderdetails.unitprice,orderdetails.quantity


select orderid,customerid,sum(total) from Purchase_by_each_customer group by customerid,orderid

为了参考:
http://www.w3schools.com/sql/sql_groupby.asp [ http://www.techonthenet.com/sql/group_by.php [ http://weblogs.sqlteam.com/jeffs/archive/2005/12/14/8546.aspx [ ^ ]

For Refrence:
http://www.w3schools.com/sql/sql_groupby.asp[^]
http://www.techonthenet.com/sql/group_by.php[^]
http://weblogs.sqlteam.com/jeffs/archive/2005/12/14/8546.aspx[^]

这篇关于在sql server 2005的联接表中使用group by的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！