Results 1 to 3 of 3

Thread: SQL Statement

  1. #1
    Join Date
    Oct 2003
    Posts
    54

    Question Unanswered: SQL Statement

    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?

  2. #2
    Join Date
    Sep 2002
    Location
    UK
    Posts
    5,171
    Provided Answers: 1

    Re: SQL Statement

    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;

  3. #3
    Join Date
    Oct 2003
    Posts
    54
    Thanks, worked like a charm...

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •