Results 1 to 8 of 8
  1. #1
    Join Date
    Jan 2003
    Posts
    1,605

    Unanswered: How to write SELECT statement with AVERAGE formula

    Hi,

    I would like to write SELECT statement which calcuates the average quantity for last 3 weeks for each week in year 2004 for each productid (please see the formula at the bottom).

    Thanks,
    Grofaty


    Code:
    ====== Detailed source data ======
    Week       ProductID      Quantity
    200352     1              130
    200352     1              120
    200353     1              100
    200353     1              110
    200401     1              150
    200401     1              140
    200402     1              130
    
    
    =========== Result data ==========
    Week       ProductID      Average
    200401     1 		  250                           
    200402     1              210
    
    
    == How to calculate the result ===
    250 = (130+120+100+110+150+140)/3
    210 = (100+110+150+140+130)/3
    Last edited by grofaty; 08-30-04 at 03:16.

  2. #2
    Join Date
    Jan 2004
    Location
    Europe, Finland, Helsinki
    Posts
    64
    Hi!

    This is NOT totally working answer, but may give some hint how continue:

    SELECT A.WEEK, A.ID, (A.QT + B.QT + C.QT) / 3
    FROM
    (SELECT WEEK, ID, SUM(QUANTITY) AS QT
    FROM WEEKLY
    GROUP BY WEEK, ID) A,
    (SELECT WEEK, ID, SUM(QUANTITY) AS QT
    FROM WEEKLY
    GROUP BY WEEK, ID) B,
    (SELECT WEEK, ID, SUM(QUANTITY) AS QT
    FROM WEEKLY
    GROUP BY WEEK, ID) C
    WHERE ( A.WEEK = B.WEEK + 1
    AND A.WEEK = C.WEEK + 2 )

    The problem is how to add one week and to get this:
    ( 200353 + 1 week = 200401 )
    You may need to have pretty complicated WHERE clause indeed!

    Cheers, Bill

  3. #3
    Join Date
    Jan 2003
    Posts
    1,605
    Hi hurmavi,

    There are two more problems:
    1. new year problem
    2. I need 52 weeks (I just wrote that I need 3 weeks to make it more simple)

    Thanks,
    Grofaty

  4. #4
    Join Date
    Jan 2003
    Posts
    4,292
    Provided Answers: 5
    Grofaty,
    I believe that this should work:

    SELECT weekid,productid,avg(quantity) from mytable where
    weekid between x and y group by weekid,productid order by weekid,productid

    x and y are your starting and ending periods.

    Andy

  5. #5
    Join Date
    Jan 2003
    Posts
    1,605
    Hi ARWinner,

    Your SQL is way to simple!!! It doesn't return the correct values.

    I have written an SQL which is two Word pages long, but it still doesn't show the correct values. I will work on it until I get correct solution. Hurmavi tip was good one.

    Thanks to you all,
    Grofaty

  6. #6
    Join Date
    Aug 2001
    Location
    UK
    Posts
    4,650
    Grofaty ,

    What datatype is the column 'week' in your table ?

    A quick thought ...(haven't thought about perf implications)

    Write two queries, one for weeks between 3 and 53 and the other for weeks 1 , 2.
    The former one should be simpler than the latter. Then UNION the output of the two ..


    Cheers
    Sathyaram

  7. #7
    Join Date
    Jun 2002
    Location
    UK
    Posts
    525
    If we can assume that every year has 53 weeks, we can do it like this...

    I've assumed that the 'week' field is of char data type. You might want to convert the final 'week' value back into year + week format but I'll leave that to you ;-)

    Code:
    with aggregated_totals (week, productid, quantity) as
    (
     select int(substr(week,1,4))*53+int(substr(week,5,2)), productid, sum(quantity)
     from yourTable
     group by week, productid
    )
    select a.week, a.productid, sum(b.quantity)
    from  aggregated_totals a
    ,     aggregated_totals b
    where a.week <= b.week + 2
    and   b.week <= a.week
    and   a.productid = b.productid
    group by a.week, a.productid
    ;
    Last edited by Damian Ibbotson; 08-31-04 at 11:23.

  8. #8
    Join Date
    Jun 2002
    Location
    UK
    Posts
    525
    ... and you might actually want to calcualte the average!

    select a.week, a.productid, sum(b.quantity) / 3

Posting Permissions

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