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

What you are asking doesn't make sense to me... AVG is an aggregate function and so is COUNT. What's wrong with the query above but just use: COUNT(*) AS avgorders?

Lets say you had 3 rows for day1, 2 rows for day2, 5 rows for day3, and 9 rows for day4... do you want to get back a single row result that tells you:

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

I don't think you can get that in a single query, and you'd be better off doing the second round of aggregation in a server side language like PHP operating on the results of the first aggregation.

Answer 2

This will do, assuming that order_time is date or datetime field ( everyone would be hapier this way ;) ). Of course there is some approximation, because oldest order can be in Friday and newest in Monday, so amount of every day of week isn't equal, but creating separate variable for every day of week will be pain in the ass. Anyway I hope it will be helpful for now.

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

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.

Answer 4

THis is the exact query I was looking for... chrismar035's answer worked... ALMOST.

I have a few days with 0 orders and it doesn't count those days and it's throwing off my AVG... anyway to rewrite it to accept days with 0 orders?