AVG(COUNT(*) - Orders By day of week query?

[easyedy]

THis query has baffled me... I've searched hte web work over a day now and I have tried numerous things.

 

I want to get the avg number of orders for every day of the week from my db. I can pull the total # with COUNT just find but I jsut can't figure out to get the AVG of COUNT on a GROUP BY. I've tried subqueries... functions... everything... nothing works... maybe someone can throw me a bone.

 

Here is the query I started with below. I know AVG(COUNT(*)) won't work but I'll leave it at that because it shows what I want to do.

 

SELECT AVG(COUNT(*)) AS avgorders, SUM(total) AS ordertotal, DAYNAME(STR_TO_DATE(order_time,'%m/%d/%Y %H:%i')) AS day FROM data GROUP BY day ORDER BY DAYOFWEEK(STR_TO_DATE(order_time,'%m/%d/%Y %H:%i')) ASC

 

Any help would be great. thanks.

[jskywalker]

did you try:

AVG(total)

 

this should work, because thats how its explained at:

http://dev.mysql.com/doc/refman/5.0/en/group-by-functions.html#function_avg

[easyedy]

I'm still hung on this after 2 weeks. I've tried everything... Can anyone trhow me a bone.

 

I'm almost there. The query below works... but there are days with 0 orders so it doesnt count that day and that throws off the average:

 

SELECT day_of_week, AVG(order_count) average_order FROM 
(
  SELECT DAYNAME(STR_TO_DATE(order_time,'%m/%d/%Y %H:%i')) day_of_week, 
         DAYOFWEEK(STR_TO_DATE(order_time,'%m/%d/%Y %H:%i')) day_num, 
         TO_DAYS(STR_TO_DATE(order_time,'%m/%d/%Y %H:%i')) date,
         COUNT(*) as order_count
  FROM data 
  GROUP BY date
) temp
GROUP BY day_of_week 
ORDER BY day_num

 

Would it be easier to make a simpler query in MySQL then run it thru some php code to compare to dates???

[jskywalker]

1) create a table with ints from 0 to 31;

CREATE TABLE `ints` (   `i` int(11) NOT NULL default '0');
insert into `ints` values (0),(1),(2),(3),.....,(31);

 

2) create a view called 'month'

create view month as select i, date_add(CURRENT_DATE,INTERVAL (i-DAY(CURRENT_DATE)+1) DAY) d from ints having month(d)=month(current_date);

 

3) after this change your query to (untested... ;-):

SELECT day_of_week, AVG(order_count) average_order 
FROM (  
    SELECT DAYNAME(STR_TO_DATE(order_time,'%m/%d/%Y %H:%i')) day_of_week,          DAYOFWEEK(STR_TO_DATE(order_time,'%m/%d/%Y %H:%i')) day_num,          TO_DAYS(STR_TO_DATE(order_time,'%m/%d/%Y %H:%i')) date,         COUNT(*) as order_count  
   FROM data
   RIGHT JOIN `month` on i=date(order_time)   
   GROUP BY date) temp
GROUP BY day_of_week 
ORDER BY day_num

[fenway]

Sorry, this seems overly complicated.  You're trying to group by the date of the week (e.g. MON/TUE)?  That's it?