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?

Greg Reynolds · Accepted Answer · 2011-12-03 13:00:44Z

3

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;

edited Dec 3, 2011 at 13:00

answered Dec 3, 2011 at 12:52

Greg Reynolds

10.5k15 gold badges53 silver badges63 bronze badges

Sign up to request clarification or add additional context in comments.

1 Comment

user1078958 Over a year ago

Thanks @Greg. It gives me just what I needed.

Mehmet Balioglu · Accepted Answer · 2011-12-03 21:00:06Z

1

That's simple with the powerful ORACLE ANALYTICAL FUNCTIONS

Below will give the max revenues for each pub_id.

  select pub_id,REV from 
   (
   select pub_id, (sales*price) as REV,
   max(sales*price) over (partition by pub_id order by 1) as MAX 
   from titles
   )
   where REV=MAX

If you want to determine the pub_id with the maximum revenue:

   select * from
   (
   select pub_id,REV from 
   (
   select pub_id, (sales*price) as REV,
   max(sales*price) over (partition by pub_id order by 1) as MAX 
   from titles
   )
   where REV=MAX order by MAX desc
   )
   where rownum<2

edited Dec 3, 2011 at 21:00

answered Dec 3, 2011 at 13:31

Mehmet Balioglu

2,3125 gold badges23 silver badges33 bronze badges

1 Comment

user1078958 Over a year ago

Thanks @bonsvr However keep getting this error "ORA-00923: FROM keyword not found where expected"

SalahTriki · Accepted Answer · 2015-12-11 12:25:13Z

1

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

answered Dec 11, 2015 at 12:25

SalahTriki

111 bronze badge

Comments

Alessandro Rossi · Accepted Answer · 2011-12-03 14:38:04Z

0

There's no really need for analytic functions in this case. The best option would be to group two times one for the sum() and the following time for the max() with the dense_rank option.

select max(pub_id) keep (dense_rank last order by TotalRevenue)
from (
        SELECT PUB_ID, SUM(SALES*PRICE) as TotalRevenue 
        FROM TITLES 
        GROUP BY PUB_ID
    )

answered Dec 3, 2011 at 14:38

Alessandro Rossi

2,44617 silver badges24 bronze badges

1 Comment

user1078958 Over a year ago

Thanks @Alessandro. Not familiar with dense_rank yet but will be soon.

Collectives™ on Stack Overflow

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

4 Answers 4

1 Comment

1 Comment

Comments

1 Comment

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

4 Answers 4

1 Comment

1 Comment

Comments

1 Comment

Your Answer

Sign up or log in

Post as a guest

Related