Average of a Top queries results?

[lyanch]

I am not sure exactly how to approach this.

I have two tables. customer and order

customer has a last contact date

I want the average of the top 10 sales within 60 days of the last contact.

I can get the top ten sales easily enough with a top ten query for the customer and date range using both tables, but to get the top ten, i have to specify the customer.

So... i decided to use this query as a subquery...

but to average it, i need another subquery

so i end up with basically:

SELECT customer.customerID, 
       customer.lastContact, 
       (select avg(AvgContactSales.topamount) 
          from (SELECT top 10 order.amount as topamount
                FROM [order]
                WHERE (((order.saledate)>[customer]![lastContact]) 
                AND ((order.saledate)<=([customer]![lastContact]+60)) 
                AND ((order.customerId)=[customer]![customerId]))
                order by order.amount desc))) AS AvgContactSales)
FROM customer;

And... access doesn't like the double level subquery asking for the value of customer ID and lastcontact.

Any suggestions?

Thanks in advance

Lisa

 

[PaulBricker]

How about something like this:

SELECT customer.customerID,
customer.lastContact,
(SELECT top 10 Avg(order.amount) as avgtopamount
FROM [order]
WHERE (((order.saledate)>[customer]![lastContact])
AND ((order.saledate)<=([customer]![lastContact]+60))
AND ((order.customerId)=[customer]![customerId]))
order by order.amount desc)))
FROM customer;

Paul

 

[lyanch]

Avg(select top 10 x) isn't the same as select top 10 avg(x)

The first averages the top 10, the second takes the top 10 averages.

Lisa

 

[rac2]

SELECT customer.customerID,
       customer.lastContact,
       (select avg(AvgContactSales.topamount)
          from (SELECT top 10 order.amount as topamount
                FROM [order]
                WHERE (((order.saledate)>[customer]![lastContact])
                AND ((order.saledate)<=([customer]![lastContact]+60))
                AND ((order.customerId)=[customer]![customerId]))
                order by order.amount desc))) AS AvgContactSales)
FROM customer
GROUP BY customer.customerID,
       customer.lastContact;

I believe that aggregate functions such as AVG() can be used in two ways. Either for an entire set of rows with no columns in the SELECT list; or with a GROUP BY clause and any columns in the SELECT list must also appear in the GROUP BY list. That suggests adding a GROUP BY clause to the query.

The prompt for customerid and lastcontact may be Access way of coping with the SQL syntax error in the query.

SELECT customer.customerID,
       customer.lastContact,
       AVG(TopContactSales.topamount)
FROM customer
JOIN (
      SELECT top 10 order.customerId,
                    order.amount as topamount
      FROM [order]
      WHERE 
          order.saledate>[customer]![lastContact]
        AND    
          order.saledate <= [customer]![lastContact]+60
        AND 
          order.customerId = [customer]![customerId]

      ORDER BY order.amount desc
     ) AS TopContactSales ON 
        TopContactSales.customerId = [customer].customerId

GROUP BY customer.customerID,
       customer.lastContact;

This suggestion is to place the subquery in a JOIN. The subquery will take the customerId from the customer table, therefore the TOP 10 amounts will be for one customer. The Avg will be over these rows for one customer because of the GROUP BY. At any rate that is my theory.

The requirement seems tricky to me so no promise that either idea will work. Let me know.

 

[PaulBricker]

Sorry, end of the day brain cramp.


Paul

 

[lyanch]

I tried the subquery as a join, it wouldn't recognize that it was correlated, and asked for the values.

Lisa

P.S. I think i am going to do this in a BO report using its top features...