using group by in tables joined together in sql server

[shanboy]

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' 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

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?

[NeoPa]

Jet SQL Help:
All fields in the SELECT field list must either be included in the GROUP BY clause or be included as arguments to an SQL aggregate function.

This quote from the Help system implies that all references to fields, even compound references, must either be aggregated (included in one of the aggregate functions - Sum(), Avg(), Min(), Max(), Count(), StDev(), Var(), First() or Last()) or included in the GROUP BY clause. Any expression which is of either type is considered aggregated. The aggregate functions can only take field reference expressions which resolve to non-aggregated fields.

Essentially, the SQL Engine needs to know how to process any particular reference. If you have a reference to a field which is not inside an Aggregate function of any type, and is also not in the GROUP BY clause, then how is the SQL Engine to know which of the many possible records in the group to take the value from.

See the situation below and ask yourself whether the result for [fB] should be "A", "B" or "C" :

SELECT   [fA]
       , [fB]
       , Sum([fC]) AS [SumC]
FROM     [Tbl]
GROUP BY [fA]

Table = [Tbl]
 [fA]  [fB]  [fC]
 1    "A"    11
 1    "B"    22
 1    "C"    33

Result :

[fA]=1, [fB]="???", [SumC]=66

Conclusion :

The fact that you cannot determine from this information which item should be selected, is an illustration of why it doesn't makes sense unless the above criteria are met (In short that all field references are aggregated in one way or another).

[NeoPa]

Actually, this is such a common confusion that I decided to prepare the answer as an article, which can be found at Aggregate Query Woes.

[shanboy]

Thanx,got the point all fields in select other than the aggregate fn should be included after group by clause.
Thanx a lot

[NeoPa]

Pleased to help. You're certainly not the first to get confused by that (I beat you to it myself some while ago :-D), nor will you be the last. I just hope the article helps to steer people in the right direction in future.