DBAsupport.com Forums - Powered by vBulletin
Page 1 of 2 12 LastLast
Results 1 to 10 of 17

Thread: sql help(finding average of last 30days)

  1. #1
    Join Date
    Mar 2001
    Location
    south africa
    Posts
    401

    sql help(finding average of last 30days)

    Hi Guys,

    need help in finding average of last 30days data

    i have orders table

    orders
    --------
    orderid number(50)
    order_date date
    parts number (20)

    info:
    orderid is unique number
    orderdate is date the order placed
    parts is number of units/parts placed in an order as customer can bundle stuff

    i am looking for a summary query where
    it has orderd date and total orders and
    average parts palced in last 30 days for each corresponding orderdate

    for example for 11/01/2006 the avg partsplaced last30days =sum(ordersplacedbetween10/02/2006to 10/31/2006)/30)

    orderdate === totalorders === avgpartslast30days
    11/01/2006 === 200 === sum(ordersplacedbetween10/02/2006to 10/31/2006)/30
    11/02/2006 === 100 === sum(parts placedbetween10/03/2006to 11/01/2006)/30
    11/03/2006 === 150 === sum(parts placedbetween10/04/2006to 11/02/2006)/30

    anyhelp is much appreciated

    thanks

  2. #2
    Join Date
    Mar 2007
    Location
    Ft. Lauderdale, FL
    Posts
    3,555
    Please take a look at AVG() function
    Pablo (Paul) Berzukov

    Author of Understanding Database Administration available at amazon and other bookstores.

    Disclaimer: Advice is provided to the best of my knowledge but no implicit or explicit warranties are provided. Since the advisor explicitly encourages testing any and all suggestions on a test non-production environment advisor should not held liable or responsible for any actions taken based on the given advice.

  3. #3
    Join Date
    Apr 2001
    Location
    Czechia
    Posts
    712
    Yes, analytic AVG() will do the job.
    I don't claim it's perfect, but with test data it works well:

    Code:
    select
       order_date,
       cnt,
       avg(cnt) over (order by order_date range 30 preceding) as average
    from
    (
       select trunc(order_date) order_date, count(*) as cnt
         from orders
        group by trunc(order_date)
    )
    /
    Ales
    The whole difference between a little boy and an adult man is the price of toys

  4. #4
    Join Date
    Mar 2001
    Location
    south africa
    Posts
    401
    thank you but its giving me incorrect data.

    sure i am missing some thing


    for example i ran for 2 different days

    #########################################
    select
    order_date, orders,
    pnt,
    avg(pnt) over (order by order_date range 30 preceding) as average
    from
    (
    select trunc(order_date) order_date, count(order_id)as orders,sum(parts) as pnt
    from orders where customer_id=8907654
    and trunc(order_date) in ('01-JAN-2009' ,'15-JAN-2009')
    group by trunc(order_date)
    )

    ORDER_D ORDERS PNT AVERAGE
    --------- ---------- ---------- ----------
    01-JAN-09 53 315.733333 315.733333
    15-JAN-09 140 615.116667 465.425

    #################################################
    i just did the simple math from excel for jan/01/09, it should give 493.5 as it sum of parts from dec022008 to dec312008/30

    same thing for 15th jan it should pick from 16th dec to 15jan 2009


    attached a text file that has everyday total orders and sum of parts

    what am i missing here

    please advise
    Attached Files Attached Files

  5. #5
    Join Date
    Mar 2007
    Location
    Ft. Lauderdale, FL
    Posts
    3,555
    I'm seeing three issues here, try this...

    1) Take out "and trunc(order_date) in ('01-JAN-2009' ,'15-JAN-2009')" condition

    2) Code your over clause as "avg(pnt) over (order by order_date range between 31 preceding and 1 preceding) as average"

    3) Encapsulate your whole statement as an inline view into a
    select *
    from
    (
    Your-whole-query
    )
    where trunc(order_date) in ('01-JAN-2009' ,'15-JAN-2009')
    ;

    By the way, my strong suggestion would be for you to take this as a learning opportunity then look at analytic queries. Your troubled query was very easy to troubleshoot -providing you know what you are coding
    Pablo (Paul) Berzukov

    Author of Understanding Database Administration available at amazon and other bookstores.

    Disclaimer: Advice is provided to the best of my knowledge but no implicit or explicit warranties are provided. Since the advisor explicitly encourages testing any and all suggestions on a test non-production environment advisor should not held liable or responsible for any actions taken based on the given advice.

  6. #6
    Join Date
    Mar 2001
    Location
    south africa
    Posts
    401
    Thank you for your reply and agreed on learning part and will do

    still incorrect data and on top of it it shows null for average for 01/01/2009
    i think its due preceeding 1 and for 01/15 it shows the average of 01/01/2009


    ORDER_D ORDERS PNT AVERAGE
    --------- ---------- ---------- ----------
    01-JAN-09 53 315.733333 NULL
    15-JAN-09 140 615.116667 315.733333

    i tried both queries and both gives same result

    select
    order_date, orders,
    pnt,
    avg(pnt) over (order by order_date range between 30 preceding and 1 preceding ) as average
    from
    (
    select trunc(order_date) order_date, count(order_id)as orders,sum(parts) as pnt
    from orders where customer_id=8907654
    group by trunc(order_date)
    ) where trunc(order_date) in ('01-JAN-2009' ,'15-JAN-2009')


    and


    select * from (
    select
    order_date, orders,
    pnt,
    avg(pnt) over (order by order_date range between 30 preceding and 1 preceding ) as average
    from
    (
    select trunc(order_date) order_date, count(order_id)as orders,sum(parts) as pnt
    from orders where customer_id=8907654
    group by trunc(order_date)
    ) where trunc(order_date) in ('01-JAN-2009' ,'15-JAN-2009') )

  7. #7
    Join Date
    Apr 2001
    Location
    Czechia
    Posts
    712
    Quote Originally Posted by prodadmin View Post
    i just did the simple math from excel for jan/01/09, it should give 493.5 as it sum of parts from dec022008 to dec312008/30

    same thing for 15th jan it should pick from 16th dec to 15jan 2009
    Now I see I made a mistake. The correct function is not AVG but SUM:

    Code:
     select
        order_date,
        orders,
        pnt,
        sum(pnt) over (order by order_date range between 30 preceding and 1 preceding)/30 as average
     from
        orders
     order by
        order_date
    /
    The result for jan/01/09 is correct - but the result for jan/15/09 should be 468.4 for range 16th dec to 14jan 2009.

    Test output based on the file you posted is here:
    Attached Files Attached Files
    Ales
    The whole difference between a little boy and an adult man is the price of toys

  8. #8
    Join Date
    Mar 2001
    Location
    south africa
    Posts
    401
    ales thank you but sorry still incorrect as i get like 10 and 15 as average not the correct ones for each date
    can it be due data i have is multiple rows unlike i presented data in summaries?

    i tried yours and it fails not a group by clause ,i did but still complains

    select
    order_date, orders,
    pnt,
    sum (pnt) over (order by order_date range between 30 preceding and 1 preceding )/30 as as average
    from
    (
    select trunc(order_date) order_date, count(order_id)as orders,sum(parts) as pnt
    from orders where customer_id=8907654
    group by trunc(order_date)
    ) where trunc(order_date) in ('01-JAN-2009' ,'15-JAN-2009')
    Last edited by prodadmin; 03-20-2009 at 04:13 PM. Reason: more text

  9. #9
    Join Date
    Mar 2007
    Location
    Ft. Lauderdale, FL
    Posts
    3,555
    Please, in the future format code by wrapping it on Code Tags

    I do not have your source data but I can tell provided solution works fine using the aggregated data you provided -a level of aggregation shouldn't affect the overall logic.

    Code:
    SQL> 
    SQL> create table mytable
      2  (
      3  order_date date,
      4  order_id number,
      5  order_sum number)
      6  ;
    
    Table created.
    
    SQL> 
    SQL> insert into mytable values('01-Dec-08', 98 ,477 );                          
    
    1 row created.
    
    SQL> insert into mytable values('02-Dec-08', 120 ,562 );                          
    
    1 row created.
    
    SQL> insert into mytable values('03-Dec-08', 166 ,671 );                        
    
    1 row created.
    
    SQL> insert into mytable values('04-Dec-08', 90 ,480 );                          
    
    1 row created.
    
    SQL> insert into mytable values('05-Dec-08', 116 ,500 );                          
    
    1 row created.
    
    SQL> insert into mytable values('06-Dec-08', 45 ,289 );                          
    
    1 row created.
    
    SQL> insert into mytable values('07-Dec-08', 55 ,287 );                          
    
    1 row created.
    
    SQL> insert into mytable values('08-Dec-08', 110 ,549 );                          
    
    1 row created.
    
    SQL> insert into mytable values('09-Dec-08', 146 ,635 );                          
    
    1 row created.
    
    SQL> insert into mytable values('10-Dec-08', 127 ,635 );                          
    
    1 row created.
    
    SQL> insert into mytable values('11-Dec-08', 113 ,536 );                          
    
    1 row created.
    
    SQL> insert into mytable values('12-Dec-08', 119 ,639 );                          
    
    1 row created.
    
    SQL> insert into mytable values('13-Dec-08', 63 ,344 );                          
    
    1 row created.
    
    SQL> insert into mytable values('14-Dec-08', 92 ,482 );                          
    
    1 row created.
    
    SQL> insert into mytable values('15-Dec-08', 114 ,551 );                          
    
    1 row created.
    
    SQL> insert into mytable values('16-Dec-08', 144 ,565 );                          
    
    1 row created.
    
    SQL> insert into mytable values('17-Dec-08', 156 ,721 );                          
    
    1 row created.
    
    SQL> insert into mytable values('18-Dec-08', 95 ,462 );                          
    
    1 row created.
    
    SQL> insert into mytable values('19-Dec-08', 111 ,429 );                          
    
    1 row created.
    
    SQL> insert into mytable values('20-Dec-08', 56 ,288 );                          
    
    1 row created.
    
    SQL> insert into mytable values('21-Dec-08', 58 ,308 );                          
    
    1 row created.
    
    SQL> insert into mytable values('22-Dec-08', 121 ,592 );                          
    
    1 row created.
    
    SQL> insert into mytable values('23-Dec-08', 102 ,481 );                          
    
    1 row created.
    
    SQL> insert into mytable values('24-Dec-08', 101 ,487 );                          
    
    1 row created.
    
    SQL> insert into mytable values('25-Dec-08', 32 ,238 );                          
    
    1 row created.
    
    SQL> insert into mytable values('26-Dec-08', 63 ,434 );                          
    
    1 row created.
    
    SQL> insert into mytable values('27-Dec-08', 77 ,513 );                          
    
    1 row created.
    
    SQL> insert into mytable values('28-Dec-08', 89 ,526 );                          
    
    1 row created.
    
    SQL> insert into mytable values('29-Dec-08', 130 ,641 );                          
    
    1 row created.
    
    SQL> insert into mytable values('30-Dec-08', 108 ,497 );                          
    
    1 row created.
    
    SQL> insert into mytable values('31-Dec-08', 95 ,463 );                          
    
    1 row created.
    
    SQL> insert into mytable values('01-Jan-09', 53 ,316 );                          
    
    1 row created.
    
    SQL> insert into mytable values('02-Jan-09', 76 ,361 );                          
    
    1 row created.
    
    SQL> insert into mytable values('03-Jan-09', 76 ,359 );                          
    
    1 row created.
    
    SQL> insert into mytable values('04-Jan-09', 60 ,331 );                          
    
    1 row created.
    
    SQL> insert into mytable values('05-Jan-09', 107 ,538 );                          
    
    1 row created.
    
    SQL> insert into mytable values('06-Jan-09', 116 ,573 );                          
    
    1 row created.
    
    SQL> insert into mytable values('07-Jan-09', 117 ,575 );                          
    
    1 row created.
    
    SQL> insert into mytable values('08-Jan-09', 124 ,539 );                          
    
    1 row created.
    
    SQL> insert into mytable values('09-Jan-09', 89 ,432 );                          
    
    1 row created.
    
    SQL> insert into mytable values('10-Jan-09', 56 ,294 );                          
    
    1 row created.
    
    SQL> insert into mytable values('11-Jan-09', 49 ,273 );                          
    
    1 row created.
    
    SQL> insert into mytable values('12-Jan-09', 133 ,571 );                          
    
    1 row created.
    
    SQL> insert into mytable values('13-Jan-09', 120 ,642 );                          
    
    1 row created.
    
    SQL> insert into mytable values('14-Jan-09', 150 ,604 );                          
    
    1 row created.
    
    SQL> insert into mytable values('15-Jan-09', 140 ,615 );                          
    
    1 row created.
    
    SQL> insert into mytable values('16-Jan-09', 99 ,524 );          
    
    1 row created.
    
    SQL> insert into mytable values('17-Jan-09', 61 ,385 );                          
    
    1 row created.
    
    SQL> insert into mytable values('18-Jan-09', 49 ,264 );                          
    
    1 row created.
    
    SQL> insert into mytable values('19-Jan-09', 115 ,604 );                          
    
    1 row created.
    
    SQL> insert into mytable values('20-Jan-09', 144 ,571 );                          
    
    1 row created.
    
    SQL> insert into mytable values('21-Jan-09', 140 ,641 );                          
    
    1 row created.
    
    SQL> insert into mytable values('22-Jan-09', 108 ,435 );                          
    
    1 row created.
    
    SQL> insert into mytable values('23-Jan-09', 114 ,526 );                          
    
    1 row created.
    
    SQL> insert into mytable values('24-Jan-09', 72 ,460 );                          
    
    1 row created.
    
    SQL> insert into mytable values('25-Jan-09', 54 ,340 );                          
    
    1 row created.
    
    SQL> insert into mytable values('26-Jan-09', 124 ,599 );                          
    
    1 row created.
    
    SQL> insert into mytable values('27-Jan-09', 128 ,567 );                          
    
    1 row created.
    
    SQL> insert into mytable values('28-Jan-09', 142 ,600 );                          
    
    1 row created.
    
    SQL> insert into mytable values('29-Jan-09', 110 ,474 );                          
    
    1 row created.
    
    SQL> insert into mytable values('30-Jan-09', 113 ,518 );                          
    
    1 row created.
    
    SQL> insert into mytable values('31-Jan-09', 53 ,284 );
    
    1 row created.
    
    SQL> commit;
    
    Commit complete.
    
    SQL> select  *
      2  from
      3  (
      4  select  order_date,  
      5          pnt, 
      6          avg(pnt) over ( order by order_date 
      7                          range between 30 preceding
      8                                    and  1 preceding) as average
      9  from 
     10          ( 
     11          select   trunc(order_date)   order_date, 
     12                   sum(order_sum)      pnt 
     13          from     mytable
     14          group by trunc(order_date) 
     15          ) 
     16  )
     17  where   trunc(order_date) in ('01-JAN-2009' ,'15-JAN-2009');
    
    ORDER_DAT        PNT    AVERAGE
    --------- ---------- ----------
    01-JAN-09        316      493.5
    15-JAN-09        615 468.433333
    
    SQL>
    Last edited by PAVB; 03-21-2009 at 07:28 AM.
    Pablo (Paul) Berzukov

    Author of Understanding Database Administration available at amazon and other bookstores.

    Disclaimer: Advice is provided to the best of my knowledge but no implicit or explicit warranties are provided. Since the advisor explicitly encourages testing any and all suggestions on a test non-production environment advisor should not held liable or responsible for any actions taken based on the given advice.

  10. #10
    Join Date
    Mar 2001
    Location
    south africa
    Posts
    401
    i apologize for the format..

    thank you for taking time to do , let me create a test table(the way you showed) and see it makes a difference

Posting Permissions

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


Click Here to Expand Forum to Full Width