Nested aggregate query help needed

I am having problems with a nested aggregate function. What it need is to find the upc ordered most often and then return its manufacturer name.

The tables are:

ORDERITEMS
ORDER#, ITEM#, UPC, QUANTITY

UPCMANU
UPC, MANID

MANUFACTURER
MANUID
NAME

There is an ORDERS table that holds the customer order info, but I don't believe it is needed for this query.

I can query to find the total times every upc has been ordered, but I am at a loss at how to find and return the most often ordered upc to an outer query to find the manuid.

select upc, sum(quantity)
from orderitems
group by upc;

Any help would be much appreciated!

To determine the number of orders per UPC you can use
SELECT upc, count(upc)
from orderitems
group upc
order by upc desc;
...
You can place this data into a temporary table then query the max value for this.
...
Or you could join the orderitems table to the manufacturer table to learn the name in the same query.
SELECT m.name, o.upc, count(o.upc)
from orderitems o, manufacturer m, upcmanu u
where o.upc = u.upc
and u.manid = m.manuid
group upc
order by upc desc;
...

My solution has three parts.

Part1 (the innermost query), adds up all of the totals for each UPC, then orders them by totals (with the record with the highest total first).

Part 2 pulls the very first row from part1 - this will be the UPC with the highest total count (if you want the top 5 UPC's, just change "ROWNUM < 2" to "ROWNUM < 6").

Part 3 joins the results of part 2 to the munfacturing tables to get the UPC's details.