SQL QUESTION on grouping and sum/avg over a 7 day time period

SQL QUESTION on grouping and sum/avg over a 7 day time period

Post by Citizen Mi » Sun, 29 Sep 2002 13:09:59



Hello again folks,

I have a table with various prices. It's something like this,

customer_id
item_id
price_1
price_2
order_qty
date

I need to "group" this table by customer_id and item_id, then average
the prices and sum the order_qty. But the problem is, I need to do
this for a 7 day period starting from the date. How can I do that ?
I'm using db2 7.2 ee.

Thanks very much - CM.

 
 
 

SQL QUESTION on grouping and sum/avg over a 7 day time period

Post by Serge Riela » Sun, 29 Sep 2002 21:14:13


Hi,

Take a look at teh OLAP expressions.
Theay are of the form:
avg(..) over (....). The over clause allows all kidn of magic including
moving averages.

Cheers
Serge

--
Serge Rielau
DB2 UDB SQL Compiler Development
IBM Software Lab, Toronto

Try the DB2 UDB V8.1 beta at
http://www-3.ibm.com/software/data/db2/

 
 
 

SQL QUESTION on grouping and sum/avg over a 7 day time period

Post by Harald Wilhel » Mon, 30 Sep 2002 18:13:22


Citizen,

do you mean current date?

Quote:> But the problem is, I need to do
> this for a 7 day period starting from the date.

 
 
 

SQL QUESTION on grouping and sum/avg over a 7 day time period

Post by Citizen Mi » Tue, 01 Oct 2002 10:05:06


Harald,

Not current date, but a date defined in the table. So I would need to,
for example, avg all the records that are in the range betwwen date
and (date+7 days)

Thank you very much - CM


> Citizen,

> do you mean current date?

> > But the problem is, I need to do
> > this for a 7 day period starting from the date.

 
 
 

SQL QUESTION on grouping and sum/avg over a 7 day time period

Post by Harald Wilhel » Tue, 01 Oct 2002 18:10:19


Citizen,

hmm, the date is a moving target. Suppose the following pseudo
table:

ArticleNr SoldOnDate Quantity
-----------------------------
        1 2002-09-01  10
        1 2002-09-02  20
        2 2002-09-01  5
        2 2002-09-02  10
        2 2002-09-03  20

What do you want to get? Perhaps I completely misunderstand
your requirement ...

 
 
 

1. Group by, Avg, Sum ???

I am trying to add upeach day's txn_cnt and divide by the total number of
days (avg for the three days, or more days if required).
  Here is the statement that gets the detail data and the data follows:
  SQL> l
select a.location_id,
a.dept_code,
a.labor_date,
to_char(a.txn_date,'DY'),
a.txn_hour,
a.txn_cnt
from txn_details a, process_dates b
where a.dept_code ='100'
and a.location_id = '011'
and a.txn_hour = 7
and to_char(a.txn_date,'DY') = 'WED'
and a.txn_date >sysdate-23
and a.txn_type in ('B','C','D','E','F','G','H','I')
and to_char(b.end_date,'YYMMDD') =
(select max(to_char(end_date,'YYMMDD'))
from process_dates
where to_char(end_date,'YYMMDD') < to_char(sysdate,'YYMMDD'))
order by a.labor_date
  SQL> /
  LOC DEP LABOR_DAT TO_ TX TXN_CNT
  --- --- --------- --- -- ----------
  011 100 11-JAN-01 WED 7   3
  011 100 11-JAN-01 WED 7   2
  011 100 11-JAN-01 WED 7  18
  011 100 11-JAN-01 WED 7   3
  011 100 11-JAN-01 WED 7   1
  011 100 18-JAN-01 WED 7   1
  011 100 18-JAN-01 WED 7   1
  011 100 18-JAN-01 WED 7  14
  011 100 18-JAN-01 WED 7   1
  011 100 25-JAN-01 WED 7   2
  011 100 25-JAN-01 WED 7   2
  011 100 25-JAN-01 WED 7   3
  011 100 25-JAN-01 WED 7   7
  011 100 25-JAN-01 WED 7   1
  14 rowsselected.

  As I requested, I would like to add each of the days txn_cnt and then avg
all.
  Here are the expected results:
  Sum each days:
  11-Jan - 3+2+18+3+1 =27
  18-Jan - 1+1+114+1 = 17
  25-Jan - 2+2+3+7+1 = 15
  Avg the days together:
  27+17+15 = 59/3 = 19.66
  So, I would like to modify the above statement to return the following
results:
  011   100   WED   7   19.66
  I tried to do an avg(txn_cnt) then grouping but it avg all the txn_cnts
and not by day.
  Any assistance would be appreciated.
  Thanks.
  Sean
_________________________________________________________________
Get your FREE download of MSN Explorer at http://explorer.msn.com

--
Posted from [12.4.83.2] by way of f48.law10.hotmail.com [64.4.15.48]
via Mailgate.ORG Server - http://www.Mailgate.ORG

2. Outbound FTP

3. SQL question: ordering based on SUM/AVG/COUNT

4. Create a query that does the opposite

5. Day Light Savings Time Switchover Periods

6. Cannot register acbtrv32.ocx

7. Sum & Avg SQL functions

8. JDBC and INFORMIX-SE

9. SQL - Sum and Group by Question

10. SQL Question - Summing and Grouping

11. Q: sql question(sum across column w/o group by)

12. Informix sql sum/group by question

13. Group-function SUM on time (date-field)