At 07:36 AM 26/02/2004 +0000, you wrote:

> > OK, here are a few variations (assuming Fb 1.5).
> >
> > invoice table
> > 1. invid
> > 2. customerid
> > 3. invdate
> > 4. totalamount
> >
> > 1. For a given customer and month:
> >
> > select avg(totalamount) as mthly_avg
> > from invoice
> > where customerid = :customerid
> > and invdate between :startdate and :enddate;
> >
> > 2. For all customers in a given calendar month:
> >
> > select customerid, avg(totalamount) as mthy_avg
> > from invoice
> > where extract(month from invdate) = :aMonth
> > group by customerid;
> >
> > 3. For all customers, by month, for a given period:
> > select
> > extract(year from invdate) as yearno,
> > extract(month from invdate) as monthno,
> > customerid,
> > avg(totalamount) as mthy_avg
> > where invdate between :startdate and :enddate
> > group by 3, 1, 2
> > order by 3, 1, 2
> >
> > /helen
>
>Iam sorry Helen if I didn't make myself clear. I have try your
>suggestion and it doesn't give me the result that I want. The sql
>above result to the average totalamount for each customer per month.
>What I need the the average monthly totalamount. Can this be
>accomplish without the need to enter parameter? Below I a sql
>statement that I have try to make and I feel a bit awful about it, if
>it is wrong can you correct or improve it.
>
>select "Cust ID", sum("Grand Total") / (cast (max("Deliver Date") -
>min("Deliver Date") as numeric(15,2)) / 30)
>from "DR"
>group by "Cust ID"
>having max("Deliver Date") - min("Deliver Date") != 0
>
>* please me reminded that there are some customer that has no invoice
>in a month which should be considered in this computation.
>
>Thanks a lot Helen. This puzzle has been taking me so long to complete it.

I can't make head nor tail of what you're trying to do. If it works and it
gets the result you're after, then it must be OK. :-)

Can't offer anything further. Can't imagine why customers who didn't buy
anything would have records in the table..I assume you have a customer
table somewhere in the periphery with a left join....whatever....

Helen