# Thread: How to write SELECT statement with AVERAGE formula

1. Registered User
Join Date
Jan 2003
Posts
1,636

## 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. Registered User
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. Registered User
Join Date
Jan 2003
Posts
1,636
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. Registered User
Join Date
Jan 2003
Posts
4,310
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. Registered User
Join Date
Jan 2003
Posts
1,636
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. Super Moderator
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

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.

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
•