Select top 10 sold items, oracle apex

Question

I'm trying to find out the top sold items from my database, I have 3 tables, se_order, se_items, se_ordered_item. I tried the following SQL but I stuck in the group by issue, I don't see where the error is:

select i.name, count(max(oi.item_id*oi.quantity))
from SE_ORDERED_ITEM oi, se_items i, se_order o 
where o.order_id = oi.order_id 
  and oi.item_id = i.item_id
group by i.name, i.item_id"

I want to list the names of top sold items along with number of these sold items

but I get this error:

ORA-00937: not a single-group group function;

so could any one please help me in this issue

Thanks In Advance

PS, I am using apex oracle environment

SE_ORDER has: order_id customer_id order_date order_status order_value se_ordered_item table has: ORDER_ID ITEM_ID QUANTITY se_items table has: ITEM_ID CATEGORY_ID DESCRIPTION VENDOR_ID PRICE ITEMS_IN_STOCK ITEM_IMAGE NAME — Sky Bird, Dec 24 '14 at 16:21

score 2 · Answer 1 · answered Dec 24 '14 at 15:56

2

Although Oracle allows nested aggregation functions, I think you want something like:

select i.name, sum(oi.quantity)
from SE_ORDERED_ITEM oi JOIN
     se_items i
     on oi.item_id = i.item_id JOIN
     se_order o 
     on o.order_id = oi.order_id 
group by i.name
order by sum(oi.quantity) desc;

Notice:

The change to the select to use sum().
The use of explicit join in the from.
The removal of oi.item_id from the group by.
The order by clause.

answered Dec 24 '14 at 15:56

Gordon Linoff

1,242,037
58
646
786

Thanks Gordon Linoff I tried this sql but it retrieves only the first top sold item, what i need is the top 10 sold items thanks again – Sky Bird Dec 24 '14 at 16:26
@SkyBird . . . This should actually return *all* the items ordered by the amount sold. You can choose as many as you like. – Gordon Linoff Dec 24 '14 at 19:07

score 0 · Answer 2 · answered Dec 31 '14 at 05:13

You may want to try rownum

select i.name, sum(oi.quantity)
from SE_ORDERED_ITEM oi, se_items i, se_order o 
where o.order_id = oi.order_id 
  and oi.item_id = i.item_id
  and rownum <= 10
group by i.name
order by sum(oi.quantity) desc;

Or if you're using APEX, just use a Dinamyc PL/SQL Region:

DECLARE
  CURSOR items is
    select i.name, sum(oi.quantity)
    from SE_ORDERED_ITEM oi, se_items i, se_order o 
    where o.order_id = oi.order_id 
      and oi.item_id = i.item_id
    group by i.name
    order by sum(oi.quantity) desc; 
  counter NUMBER;
BEGIN
  counter := 0;

  for i in items loop
    counter := counter + 1;
    htp.p(i.name);    
  exit when counter = 10; 
  end loop;
END;

Select top 10 sold items, oracle apex

2 Answers2