MySQL AVG (COUNT (*) - orders by day of the week?

Question

This query puzzled me ... I was looking for web work in a day, and I tried a lot of things.

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

Here is the query that I started with below. I know that AVG (COUNT (*)) will not work, but I will leave it 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

, , .

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

. I.e(5 + 6)/2 .
- :

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

: . SELECT , . , , (2/1/10) (2/8/10) . .

Answer 2

, , order_time - date datetime ( ;)). , , , , . , , .

SET @total_weeks = (
    SELECT
        TIMESTAMPDIFF(
            WEEK,
            MIN(order_time),
            MAX(order_time)
        )
     FROM data
    );

SELECT
    DAYNAME(order_time) AS day_of_week,
    ( COUNT(*) / @total_weeks ) AS avgorders,
    COUNT(*) AS total_orders
FROM 
    data
GROUP BY
    DAYOFWEEK(order_time)

Answer 3

, , , -, - . sub , , - !

SELECT dayofweek(`timestamp`) as 'Day',count(`OrderID`)/count(DISTINCT day(`timestamp`)) as 'Average' FROM  `Data` GROUP BY dayofweek(`timestamp`)

, , "" . , , , . .

Answer 4

, , ... AVG - , COUNT. , : COUNT (*) AS avgorders?

, 3 1, 2 2, 5 3 9 4... , :

avgorders   = (3 + 2 + 2 + 5 + 9) / 5 = 21 / 5 = 4.2
ordertotal  = (3 + 2 + 2 + 5 + 9)              = 21

, , , PHP, .