Subquery - SQL Server Forums

Please start any new threads on our new site at https://forums.sqlteam.com. We've got lots of great SQL Server experts to answer whatever question you can come up with.

All Forums

SQL Server 2000 Forums

SQL Server Development (2000)

Subquery

Author

Topic

oc1979
Starting Member

3 Posts

Posted - 2006-05-19 : 21:47:55

I'm relatively new to SQL server, and I'm studying for the Microsoft 70-229 exam. I'm studying subqueries and I was wondering if anyone knows how to write a query that returns CustomerID and the ID and amount of the largest order for that customer (based on Northwind database) - but in one statement, using subqueries and aggregates. Is it even possible? I've been struggling with this for a couple hours but can't get it easily, even with a view - still can't get the right columns to show up: CustomerID, orderID, OrderAmount (but note - not just list all orders for each customer with their total, but just the largest order)

Thanks in advance for any suggestions!

oc1979
Starting Member

3 Posts

Posted - 2006-05-19 : 22:28:26

I think I got it. This was tough. Here it goes, if anyone interested:

Select c.customerid, o.orderid, sum(unitprice*quantity) as OrderTotal
from customers c, orders o, [order details] od
where c.customerid=o.customerid
and o.orderid=od.orderid
group by c.customerid, o.orderid
having sum(unitprice*quantity)=
(Select max(sub2.total)
from
(select sum(unitprice*quantity) as total
from [order details] od3, orders o3, customers c3
where od3.orderid=o3.orderid
and o3.customerid=c3.customerid
and c3.customerid=c.customerid
group by c3.customerid, o3.orderid) as sub2)
order by c.customerid

khtan
In (Som, Ni, Yak)

17689 Posts

Posted - 2006-05-20 : 00:42:35

"but note - not just list all orders for each customer with their total, but just the largest order"
But your query returns more than one order.


Select 	c.CustomerID, o.OrderID, sum(UnitPrice * Quantity) as OrderTotal
from 	Customers c inner join Orders o
	on	c.CustomerID = o.CustomerID
	inner join [Order Details] od
	on	o.OrderID = od.OrderID
group by c.CustomerID, o.OrderID
having 	sum(UnitPrice * Quantity) = 
	(select top 1 sum(UnitPrice * Quantity) as total
		from 	Orders x inner join [Order Details] y
		on	x.OrderID	= y.OrderID
		group by x.OrderID
		order by total desc)

Result :
QUICK	10865	17250.0000

oc1979
Starting Member

3 Posts

Posted - 2006-05-20 : 07:11:33

Actually what I intended initially is to return each customer and their corresponding largest order and its orderID. "but note - not just list all orders for each customer with their total, but just the largest order for each customer" - I should have said.

But your query returns the customer with the largest order of all - also a business problem, just a slightly different one. Thanks for the advice!

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources