use aggregate on aggregate - 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

Transact-SQL (2000)

use aggregate on aggregate

Author

Topic

AskSQLTeam
Ask SQLTeam Question

0 Posts

Posted - 2003-02-19 : 07:49:18

armada writes "hello.. I have been trying to use an aggregate ontop of another aggregate. For example, to find max on count or max on sum. The following is a quick example to show it:
There are two tables:
Customers(cid,name,age)
Orders(oid,cid)
So for example, we want to find who made the most orders.
What I tried to do was to count the number of orders of each
customer and then find the max out of it. And I only want to
select its name & cid. First it's pretty easy to count:

SELECT C.cid,C.name,COUNT(O.oid)
FROM Customers C,Orders O
WHERE C.cid=O.cid
GROUP BY C.cid,C.cname

I tried to wrap it up with making above select as a derived table, but I was still not successful.
Can you please help me with a pretty efficient way of doing it? Thank you."

nr
SQLTeam MVY

12543 Posts

Posted - 2003-02-19 : 11:00:21

select top 1 *
from
(SELECT C.cid,C.name,cnt = COUNT(O.oid)
FROM Customers C,Orders O
WHERE C.cid=O.cid
GROUP BY C.cid,C.cname) as a
order by cnt desc

==========================================
Cursors are useful if you don't know sql.
DTS can be used in a similar way.
Beer is not cold and it isn't fizzy.

X002548
Not Just a Number

15586 Posts

Posted - 2003-02-19 : 11:05:39

Well...I don't know if there's an easier way, but this works:

USE Northwind

SELECT c1.CustomerID, c1.CompanyName, count(*) As Order_Count_1
FROM customers c1
INNER JOIN orders o1
ON c1.CustomerID = o1.CustomerID
GROUP BY c1.CustomerID, c1.CompanyName
HAVING Count(*) = (SELECT Max(Order_Count_2) As Max_Order_Count
FROM (SELECT c2.CustomerID, c2.CompanyName, count(*) As Order_Count_2
FROM customers c2
INNER JOIN orders o2
ON c2.CustomerID = o2.CustomerID
GROUP BY c2.CustomerID, c2.CompanyName) As xxx)

Good luck

Brett

8-)

X002548
Not Just a Number

15586 Posts

Posted - 2003-02-19 : 11:07:33

Wow, am thinking to hard...Thanks nr

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources