java max sum,使用max（）和sum（）

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?

解决方案

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;