Summarizing data in a SELECT statement using a GROUP
BY clause is a very common area of difficulty for beginning SQL programmers. In Part I of this two part series, we'll use a simple schema and a typical report request to cover the effect of JOINS on grouping and aggregate calculations, and how to use COUNT(Distinct) to overcome this. In Part II, we'll finish up our report while examining the problem with SUM(Distinct) and discussing how useful derived tables can be when grouping complicated data.

select customer, sum(itemcount) AS itemcount, sum(orderamount) as orderamount,
count(distinct orderid) as ordercount, sum(totalshipping) as totalshipping
from (
select  
  o.Customer, count(*) as ItemCount, sum(od.Amount) as OrderAmount, 
  o.OrderID, sum(o.ShippingCost) / count(*)  as TotalShipping
from 
  @Orders o
inner join 
  @OrderDetails od on o.OrderID = od.OrderID
group by 
  o.Customer, o.orderid
) as d
group by customer

quote:

---

Originally posted by Peso

Great article!

I hope the solution is far better than using a derived table like this

select customer, sum(itemcount) AS itemcount, sum(orderamount) as orderamount,
count(distinct orderid) as ordercount, sum(totalshipping) as totalshipping
from (
select  
  o.Customer, count(*) as ItemCount, sum(od.Amount) as OrderAmount, 
  o.OrderID, sum(o.ShippingCost) / count(*)  as TotalShipping
from 
  @Orders o
inner join 
  @OrderDetails od on o.OrderID = od.OrderID
group by 
  o.Customer, o.orderid
) as d
group by customer

---

E 1205.25"
N 5639.16"

---

quote:

---

Originally posted by bhaaskar

My first post here: if this has already been answered, kindly point me there. Thank you very much. From the tables Orders and order details tables mentioned in the article (http://www.sqlteam.com/article/how-to-use-group-by-in-sql-server) I would like to get: for each order, the item name of the max priced product in that order.
The result set would be:

orderID customer orderdate shippingCost DetailID Item Amount
----------- ---------- -------------- ------------- --------- ---------- ----------
1 ABC 2007-01-01 40.0000 2 Item B 150.0000
2 ABC 2007-01-02 30.0000 5 Item H 200.0000
3 ABC 2007-01-03 25.0000 6 Item X 100.0000
4 DEF 2007-01-02 10.0000 8 Item Z 300.0000

I am using SQL7.0. SQL2000 solution would work too.

---

SELECT o.OrderID,od.Item,od.Amount
FROM Orders o
INNER JOIN OrderDetails od
ON od.OrderID = o.OrderID
INNER JOIN (SELECT OrderID,MAX(Amount) AS MaxAmt
            FROM OrderDetails 
            GROUP BY OrderID)od1
ON od1.OrderID = od.OrderID
AND od1.MaxAmt = od.Amount