How to Select Average Values

[melisimp]

I am trying to select the following averages from the tables below.
average order turn time in minutes (completion_time - order_time), average fee of the final invoice, and the average # of invoices based on the following two tables:

row_id doc_id order_time completion_time
----------- -------------------- ------------------------------------------- -------------
1 Doc1 2007-03-20 10:32:07.697 2007-03-20 1:44:20.697
2 Doc2 2007-03-20 10:41:33.243 2007-03-20 1:47:06.243
3 Doc3 2007-03-20 11:06:33.260 2007-03-20 1:48:46.260
4 Doc4 2007-03-20 11:14:53.260 NULL



row_id doc_id Invoice fee
----------- -------------------- ----------- ---------------------
1 Doc1 1 .0000
2 Doc1 2 200.0000
3 Doc2 1 215.0000
4 Doc3 1 222.0000

I need the results to look like:
AVG Minutes AVG Fee AVG #Invoices
__________ _________ _____________
X Y Z

I am struggling. All I've come up with is:

SELECT AVG(TIMESTAMPDIFF(MINUTE,completion_time,order_tim e)) As AVG Minutes FROM Table 1, AVG(fee) As AVG Fee Where Invoice = “2”, AVG(Invoice) As AVG #Invoices

[zottty]

I am trying to select the following averages from the tables below.
average order turn time in minutes (completion_time - order_time), average fee of the final invoice, and the average # of invoices based on the following two tables:

row_id doc_id order_time completion_time
----------- -------------------- ------------------------------------------- -------------
1 Doc1 2007-03-20 10:32:07.697 2007-03-20 1:44:20.697
2 Doc2 2007-03-20 10:41:33.243 2007-03-20 1:47:06.243
3 Doc3 2007-03-20 11:06:33.260 2007-03-20 1:48:46.260
4 Doc4 2007-03-20 11:14:53.260 NULL



row_id doc_id Invoice fee
----------- -------------------- ----------- ---------------------
1 Doc1 1 .0000
2 Doc1 2 200.0000
3 Doc2 1 215.0000
4 Doc3 1 222.0000

I need the results to look like:
AVG Minutes AVG Fee AVG #Invoices
__________ _________ _____________
X Y Z

I am struggling. All I've come up with is:

SELECT AVG(TIMESTAMPDIFF(MINUTE,completion_time,order_tim e)) As AVG Minutes FROM Table 1, AVG(fee) As AVG Fee Where Invoice = “2”, AVG(Invoice) As AVG #Invoices

I don't really understand the 3rd,average # of invoices part, but the following query is probably a good starting point:

 SELECT 
AVG(TIMESTAMPDIFF(MINUTE,completion_time,order_time)) As AVGMinutes 
AVG(fee) as AVGFee
FROM Table1 
LEFT JOIN Table2
USING(doc_id)
 

I guess if you want to get the # of invoces, you probably need to break down/group your query and use nested selects/stored procedures.

z

[Michele]

Hi,

If I'm understanding this right, I think you might get into trouble joining the two tables on doc ID because you have a many to one relationship (e.g. Doc1 is listed two times in the second table) and that's going to screw up your aggregations.

What you want to do is aggregate table 1 and then aggregate table 2 and then join the results. The easiest way to do this is by creating two intermediate tables and then joining them. You can also used subqueries and derived tables, but you probably want to check your sums at each step so I'd make some tables (at least at the first attempt).

Are you also looking for a count(*) for the number of invoices? (e.g. sum the invoice amount for the group and then divide by count(*) to get the average invoice amount.)

Michele