Group by in sql server tables joined together

[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?
Plz help me

[darkagn]

Ok so your aggregate functions are anything appearing in the SELECT part of the statement that SUM, COUNT, AVG, MAX, MIN etc etc over one or more columns. For example, in your SQL query:

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

the SUM(orderdetails.unitprice*orderdetails.quantity) is an aggregate function. It aggregates (in this case sums) the function ( orderdetails.unitprice*orderdetails.quantity ) over all the rows. But it needs to know how to group the summing. That's where the GROUP BY clause comes in. Here's the golden rule of grouping:

Any column that is in the SELECT part of your query that is not in an aggregate function MUST be included in the GROUP BY comma-separated list.

Your query therefore needs to include the following columns in the GROUP BY clause:

orders.orderid,orders.customerid,orders.orderdate,orders.shipcity,orders.shipcountry,
orderdetails.unitprice,orderdetails.quantity

If you want to sum the orders by customer you would put customerid first in the list. So your query will look something like this:

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

Have a play around with the order of the columns in that query, and try adding/removing columns and see how the aggregate result changes. You will soon discover how grouping affects the results.

[anuj6928]

just post the snapshots of ur table structure ...

[anuj6928]

both the tables ... order and order details

[adam_k]

darkagn is correct. You need to group by all the fields that don't aggregate.

You might want to take into account the number of fields you are selecting (and grouping by) together with the sum.
In your statement (with the correct group by) you'll get the sum per order per customer per date per city per country per unitprice per quantity

This is not what you've stated that you are trying to accomplish

i wanted to join order and order details tables and calculate the total purchase made by each customer by using group by clause.

In order to get the sum of the all purchases a customer made you'd need to select and group by customer and if city and country are unique per customer (and not 1 customer with multiple ship-to locations) you can add them as well.

Give it a go and see what results you get. If you need the rest of the data then you'll need to calculate the sum in a separate query and join in to your main one.

[shanboy]

Thank Darkagn simple and clear explanation.Thanx a lot...