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

Solution 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.

Solution 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)

Solution 3

I know this is old, but i was searching for a similar solution hoping to find something someone else had used. In hopes of not doing a sub query, i came up with the below and would love any feed back!

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

The idea is to divide the total orders on a given day of the week, by the total number of "Mondays" or whatever day it is. What this does not account for would be any days that had zero orders would not lower the average. That may or may not be desired depending on the application.

Comments

[Admin]

Hmmm... maybe I explained it wrong. I can the total number of orders ever place on say a Monday... but what I want is the AVG # of orders placed on Mondays from all the orders in the DB. I can get the total # of orders palced on Mondays, Tuesdays, etc but I need the average. As below: Total Orders By Day of the Week 195 Monday 188 Thursday 180 Tuesday 179 Wednesday

[Admin]

Yes thats exactly what I need... but that query spit out totals per day again. I'll try to rework it.

[codegoalie]

Edy, You are right, what I had before was wrong. This should be what you were asking.

[Don McCurdy]

This assumes you have at least one order every day. If not, your averages will assume the day didn't exist, rather than treating it as a 0, and may skew high for these days. Still a helpful answer, but this is something to be aware of.