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: 
maybe I'm being too simplistic, but given that you're already grouping by weekday, wouldn't just count(\*) compute the number of the given weekday in that range, for every weekday?

so:

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

```

edited so not overcounting multiple times the same day (thanks DRapp)
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);

```