Question: My MySQL table has the following columns: datetime, price\_paid.
I'm trying to calculate the average sales per day of the week, between 2 dates. 
This means I need select the sum of sales, between the dates startDate and endDate, grouped by weekday and divide that by the number of times every weekday occurs in that range.

I got the first part:

```
SELECT DATE_FORMAT(datetime, '%a') AS field1, 
round(SUM(price_paid)/ THEVALUEIMLOOKINGFOR ) AS field2 
FROM Bills 
WHERE date(datetime) BETWEEN '2012-01-02' AND '2012-01-09' 
GROUP BY weekday(datetime)

```

What I don't have is that missing value: the number I should divide by - number of times each day appeared in that range. I tried several solutions, to no avail. 

Can someone please help me complete my SQL statement?

Thanks for your time!

Answer: 
I know this is old but "seems to work". isn't quite good enough. 
In fact, It will under count the number of Mondays if there was a day with ZERO transactions. 

There is a formula for counting days of week in a date range. You need to start with a known day of the week. "1970-01-05" was a MONDAY.

Here is a function you can use:

```
create function CountDaysOfWeek(
    DateFrom DATETIME,
    DateTo DATETIME,
    DayOfWeek int
)
RETURNS int
return FLOOR(DATEDIFF(DateTo,'1970-01-03' + INTERVAL DayOfWeek DAY)/7) - 
   FLOOR(DATEDIFF(DateFrom,'1970-01-03' + INTERVAL (DayOfWeek+1) DAY)/7);

```
Since you want a sum of averages then you need to use a subquery to compute the averages for each day, then you compute the sum of the averages. 

I have modified the query to include an external query which returns the total of the averages. However now the field1 will not make sense anymore since it can be any of the dates depending on which ordering option you use 

```
SELECT field1, SUM (field2) FROM (
  SELECT DATE_FORMAT(datetime, '%a') AS field1, 
  AVG(price_paid) AS field2 
  FROM Bills 
  WHERE date(datetime) BETWEEN '2012-01-02' AND '2012-01-09' 
  GROUP BY weekday(datetime)
) 

```