How to write SELECT statement with AVERAGE formula

grofaty · #1 (**permalink**) 08-30-04, 01:51

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

hurmavi · #2 (**permalink**) 08-30-04, 02:31

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

grofaty · #3 (**permalink**) 08-30-04, 03:45

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

ARWinner · #4 (**permalink**) 08-30-04, 08:23

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

grofaty · #5 (**permalink**) 08-31-04, 08:52

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

sathyaram_s · #6 (**permalink**) 08-31-04, 09:47

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

Damian Ibbotson · #7 (**permalink**) 08-31-04, 10:21

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
;

Damian Ibbotson · #8 (**permalink**) 08-31-04, 11:00

... and you might actually want to calcualte the average!

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

Internet Services

Web Development

Digital Marketing

Consumer Tech