Using Max() on SUM() Aggregate function in Oracle

Question

I am trying to write a query in Oracle which will return both the pub_id and the maximum total revenue from a titles table which lists pub_id, sales, price. I can get either a listing with pub_id and total revenues for each pub_id with

 SELECT PUB_ID, SUM(SALES*PRICE) as TotalRevenue FROM TITLES GROUP BY PUB_ID;

Or I can get just the MAX(Sales*Price) with

 SELECT MAX(SUM(sales*price)) FROM titles GROUP BY pub_id;

Any ideas how can I get the pub_id out with the maximum of the total revenue?

Answer 1

You can use the rank function like this

select * from
(
select a.*,rank() over (order by sum_sales desc) r from
(
select pub_id,sum(sales*price) sum_sales from titles group by pub_id
) a
)
where r = 1;