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)
Here is a stored function called MakeDateList: 

```
DELIMITER $$
DROP FUNCTION IF EXISTS `junk`.`MakeDateList` $$

CREATE FUNCTION `junk`.`MakeDateList`
(
  BeginDate DATE,
  EndDate DATE,
  InclusionList VARCHAR(20)
)

RETURNS VARCHAR(4096)
DETERMINISTIC
BEGIN
  DECLARE RunningDate DATE;
  DECLARE rv VARCHAR(4096);
  DECLARE comma CHAR(1);
  DECLARE IncList,DOWValue VARCHAR(20);
  DECLARE OK_To_Add INT;

  SET IncList = CONCAT(',',InclusionList,',');
  SET comma = '';
  SET rv = '';
  SET RunningDate = BeginDate;

  WHILE RunningDate <= EndDate DO
    SET OK_To_Add = 0;
    SET DOWValue = CONCAT(',',DAYOFWEEK(RunningDate),',');
    IF LOCATE(DOWValue,IncList) > 0 THEN
      SET OK_To_Add = 1;
    END IF;
    IF OK_To_Add = 1 THEN
       SET rv = CONCAT(rv,comma,RunningDate);
    END IF;
    SET comma = ',';
    SET RunningDate = RunningDate + INTERVAL 1 DAY;
  END WHILE;
RETURN rv;
END $$
DELIMITER ;

```

It has three parameters: BeginDate, EndDate, and InclusionList

InclusionList is a comma speararted list of numbers (valid numbers 1-7)

```
1 = Sunday
2 = Monday
3 = Tuesday
4 = Wednesday
5 = Thursday
6 = Friday
7 = Saturday

```

So to get Mondays, make the InclusionList 2

Example : Calling MakeDateList('2011-10-01','2011-10-01','2') should give you Mondays for the whole month of October 2011