Topic: 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.

Code: [Select]

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:

Code: [Select]

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;

Code: [Select]

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'

Code: [Select]

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... ;-):

Code: [Select]

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?