I have two tables with the following structure:

STORE_ORD Table

STORE_ORD_NUM ORD_DT
--------------------- -------------------
23762 2003-07-01 00:00:00
23763 2003-07-01 00:00:00
23764 2003-07-01 00:00:00
23765 2003-07-01 00:00:00


STORE_ORD_LN Table

STORE_ORD_NUM VSN ITM_CD SKU_NUM ORD_QTY
------------- -------------------- ------------ ------------ -------
25062 DARREN 02 000001257 000001257-04 1
25063 DARREN 02 000001257 000001257-04 1
25065 DARREN 02 000001257 000001257-04 1
25066 DARREN 02 000001257 000001257-04 1
25067 DARREN 02 000001257 000001257-04 1
25068 DARREN 02 000001257 000001257-04 1

I'm using the following query:

select sku_num, itm_cd, vsn, sum(ord_qty)
from store_ord_ln, store_ord
where store_ord_ln.store_ord_num=store_ord.store_ord_num
and store_ord.ord_dt between '04-DEC-2003' and '04-JAN-2004'
group by sku_num, itm_cd, vsn, ord_qty
having sum(ord_qty) > 500
order by ord_qty

I'm trying to query a period of time for the sum of "ord_qty" for each "sku_num" that has a value greater than 500 total, then sort then from hightest to lowest. This query will eventually be placed in SQL Plus where the user can give it the date range and the value of "sum(ord_qty)". Presently this query runs for very limited date ranges, but does not order the "sku_num" by "ord_qty". I'd like to be able to tell only see the top 50 listings or allow the user to see a specific number of entries decided at runtime?

You cannot order an aggregate query by data that is not in the result, like ord_qty. Also, you don't want to include ord_qty in the group by clause, otherwise the sum function is pointless.

You can order by sum(ord_qty) though, as that has been selected:

select sku_num, itm_cd, vsn, sum(ord_qty)
from store_ord_ln, store_ord
where store_ord_ln.store_ord_num=store_ord.store_ord_num
and store_ord.ord_dt between '04-DEC-2003' and '04-JAN-2004'
group by sku_num, itm_cd, vsn
having sum(ord_qty) > 500
order by sum(ord_qty);

Now to restrict to the "top n" orders you need to order by the sum in descending order and then skim off the first n rows like this:

select sku_num, itm_cd, vsn, sum_ord_qty
from
(
select sku_num, itm_cd, vsn, sum(ord_qty) sum_ord_qty
from store_ord_ln, store_ord
where store_ord_ln.store_ord_num=store_ord.store_ord_num
and store_ord.ord_dt between '04-DEC-2003' and '04-JAN-2004'
group by sku_num, itm_cd, vsn
having sum(ord_qty) > 500
order by sum(ord_qty) desc
)
where rownum <= &n;

Thanks, worked like a charm...