using max() and sum()

Question

I need to find the 'name of the branch that has made the most money in 2009'. My tables are below:

Rental
(cid, copyid, outdate, returndate, cost)

Copy
(copyid, mid, bid)

Branch
(bid, bname, baddress)

I have written the following code, and it outputs the sum of all branches, but I need the sum of the branch that made the most money. I am not sure how to join a max() and sum() function in the same query. I am using Oracle 2007.

Output the branch name and sum (I get a summary of all branches with this):

SELECT bname, sum(cost) as Total
FROM rented R join copy C on R.copyid = C.copyid join branch B on C.bid = B.bid
WHERE outdate between '20090101' and '20091231'
GROUP BY bname;

Output the max of sum (I don't get my branch name with this):

SELECT sum(total_cost)
FROM (SELECT max(cost) as total_cost FROM rented WHERE outdate between '20090101' and '20091231') x;

How can I merge these two together to get only the max sum branch name?

Answer 1

Why not just grab the top row from your first query?

   SELECT * FROM (
            SELECT bname, sum(cost) as Total
            FROM rented R join copy C on R.copyid = C.copyid join branch B on C.bid = B.bid
            WHERE outdate between '20090101' and '20091231' 
            GROUP BY bname
            ORDER BY sum(cost) desc) 
   WHERE ROWNUM=1;

One thing you may be forgetting however, is that two branches could tie for making the most money. If you want to include all branches that participate in the tie for 1st place you might go with something like this (Oracle 9i or later).

    SELECT * FROM (
           SELECT bname, 
           sum(cost) as Total, 
           RANK() OVER (ORDER BY sum(cost) desc) "Rank"
           FROM rented R 
           join copy C on R.copyid = C.copyid 
           join branch B on C.bid = B.bid
           WHERE outdate between '20090101' and '20091231'
          GROUP BY bname) 
     WHERE Rank=1;

Answer 2

Lots of ways. For example, preserving your original query, you can order the data by the sum and then just take the first row:

select * from (
SELECT bname, sum(cost) as Total 
FROM rented R join copy C on R.copyid = C.copyid join branch B on C.bid = B.bid 
WHERE outdate between '20090101' and '20091231' 
GROUP BY bname
order by sum(cost) desc
)
where rownum = 1;