Results 1 to 8 of 8
  1. #1
    Join Date
    Nov 2004
    Posts
    19

    Question Unanswered: Select Statement... help please

    hi all gurus!

    i am in problem please help me.

    Table:
    ------
    sale_date
    sale_time
    sale_qty
    item_code

    i wrote query as:

    select to_char(sale_date,'MON-YY') MONTH,
    max(sale_qty) highest_qty
    from ( select sale_date, sum(sale_qty) sale_qty
    from sale_trades
    where sale_date between '01-jan-04' and '31-dec-04'
    group by sale_date)
    group by to_char(sale_date,'MON-YY')
    order by 2 desc

    i want to retrieve the DATE for which i retrieved HIGHEST_QTY the data should be as:

    MONTH DATE HIGHEST_QTY
    ------- --------- -------------
    JAN-04 17-JAN-04 1000

    and another one to be retrieve with TIME as:

    MONTH DATE TIME HIGHEST_QTY
    ------- --------- -------- --------------
    JAN-04 17-JAN-04 110315 350

    thanks for the help.

  2. #2
    Join Date
    Jun 2004
    Location
    Liverpool, NY USA
    Posts
    2,509
    If you want to list all the dates that had the highest quantity, then do the following.

    Code:
    select to_char(sale_date,'MON-YY') MONTH,
             to_char(sale_date,'dd-Mon-rr') SALE_DATE,
             sale_qty highest_qty
    from   sale_trades
    where sale_qty = 
    (select max(sale_qty)
    from sale_trades
    where sale_date between to_date('01-jan-2004','dd-mon-yyyy') and to_date('31-dec-2004','dd-mon-yyyy') )
    order by sale_date;
    Bill
    You do not need a parachute to skydive. You only need a parachute to skydive twice.

  3. #3
    Join Date
    Nov 2004
    Posts
    19
    thank you for your reply beilstwh
    actually i need it as the TOP selling days of every month
    a top day in January
    a top day in Feb
    a top day in Mar
    etc.
    12 rows to be retrieve for a year (month wise)

    your Select statement is giving me the result of highest_qty in a year. if you can help, i will be much thankful.

  4. #4
    Join Date
    Jun 2004
    Location
    Liverpool, NY USA
    Posts
    2,509
    Sure. The following code will return what your want, with one limitation. If there are more then one day a month with the highest quantity, all the days will be returned. This could be changed, but the information is more accurate.

    Code:
    select to_char(sale_date,'MON-YY') MONTH,
             to_char(sale_date,'dd-Mon-rr') SALE_DATE,
             sale_qty highest_qty
    from   sale_trades
    where (sale_qty,to_char(sale_date,'MMYYYY')) = 
    (select max(sale_qty),to_char(sale_date,'MMYYYY')
    from sale_trades
    where sale_date between to_date('01-jan-2004','dd-mon-yyyy') and to_date('31-dec-2004','dd-mon-yyyy') 
    group by to_char(sale_date,'MMYYYY') )
    order by sale_date;
    Last edited by beilstwh; 02-18-05 at 12:40.
    Bill
    You do not need a parachute to skydive. You only need a parachute to skydive twice.

  5. #5
    Join Date
    May 2004
    Location
    Dominican Republic
    Posts
    721
    - What if two dates have exactly the highest qty on a month ?
    - What is this strange time thing you are refering to ?
    - How about if you put some example data ( input/output ) as well..

  6. #6
    Join Date
    Nov 2004
    Posts
    19
    many thanks again to beilstwh
    i used your second one but it returns error: single-row subquery returns more than one row i replace = (equal) sign with IN and it return that no rows selected

    now i wrote this query and it works for me on same data

    Code:
    select dt, a.sale_qty, max_sale, b.sale_date
    from (select to_char(sale_date,'yymm') dt, sum(sale_qty) sale_qty, max(sale_qty) max_sale
            from (select sale_date, sum(sale_qty) sale_qty
                    from sale_trades
                   group by sale_date)
           group by to_char(sale_date,'yymm') ) a,
         (select sale_date, sum(sale_qty) sale_qty
            from sale_trades
           group by sale_date) b
    where b.sale_qty = a.max_sale
      and to_char(b.sale_date,'yymm') = a.dt
    order by dt

    -: INTERNET IS NOTHING WITHOUT SHARING :-

  7. #7
    Join Date
    Jun 2004
    Location
    Liverpool, NY USA
    Posts
    2,509
    Are you sure that you replaced the correct =. Try

    Code:
    select to_char(sale_date,'MON-YY') MONTH,
             to_char(sale_date,'dd-Mon-rr') SALE_DATE,
             sale_qty highest_qty
    from   sale_trades
    where (sale_qty,to_char(sale_date,'MMYYYY')) IN
    (select max(sale_qty),to_char(sale_date,'MMYYYY')
    from sale_trades
    where sale_date between to_date('01-jan-2004','dd-mon-yyyy') and to_date('31-dec-2004','dd-mon-yyyy') 
    group by to_char(sale_date,'MMYYYY') )
    order by sale_date;
    Bill
    You do not need a parachute to skydive. You only need a parachute to skydive twice.

  8. #8
    Join Date
    Nov 2004
    Posts
    19
    dear beilstwh
    i mentioned in my last post that i wrote the query that worked for me as i want it to. i really appreciate and very much thankful for your help and struggle to solve my problem.
    wish you all the best brother.

Posting Permissions

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