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

Question

This query has baffled me... I've searched the 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 fine. But I just can't figure out how 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

Answer 1

To get the average you don't need the grand totals for each day, you need multiple daily totals for each day.

  Day    |  Count
__________________
 Monday        5
 Tuesday       4
 Monday        6
 Tuesday       3
 ...          ...

Then you can average those numbers. I.e (5+6)/2 for Monday.
Something like this should work:

SELECT day_of_week, AVG(order_count) average_order FROM 
(
  SELECT DAYNAME(order_date) day_of_week, 
         DAYOFWEEK(order_date) day_num, 
         TO_DAYS(order_date) date,
         count(*) order_count
  FROM data 
  GROUP BY date
) temp
GROUP BY day_of_week 
ORDER BY day_num

UPDATE: I was originally wrong. Group the inner SELECT by the actual date to get the correct daily totals. For instance, you need to get how many orders happened Monday (2/1/10) and Monday (2/8/10) separately. Then average those totals by the day of the week.