MySQL SUM Query daily values of a week problem

Question

Am trying to return the sum of each day of a week in mysql but it returns nothing despite having values for the 3rd Week of March 2010

SELECT SUM(expense_details_amount) AS total 
  FROM expense_details
WHERE YEAR(expense_details_date) = '2010'                   
  AND MONTH(expense_details_date) = '03'
  AND WEEK(expense_details_date) = '3'
GROUP BY DAY(expense_details_date)

How do I go about this?

Answer 1

According to the MySQL docs for WEEK:

This function returns the week number for date. The two-argument form of WEEK() allows you to specify whether the week starts on Sunday or Monday and whether the return value should be in the range from 0 to 53 or from 1 to 53. If the mode argument is omitted, the value of the default_week_format system variable is used. See Section 5.1.4, “Server System Variables”.

Their examples are:

mysql> SELECT WEEK('2008-02-20');
        -> 7
mysql> SELECT WEEK('2008-02-20',0);
        -> 7
mysql> SELECT WEEK('2008-02-20',1);
        -> 8
mysql> SELECT WEEK('2008-12-31',1);
        -> 53

So you should not be checking for week 3, but whatever week of the year the 3rd week in march is.

It also looks like these functions return integers instead of strings, so you might want to lose the single-quotes in your query.

Answer 2

Your where clause excludes all of the data, as the third week of the year is in January and you have also specified the month to be March. Try using a where clause of the form:

WHERE expense_details_date BETWEEN '2010-03-15' AND '2010-03-22'

Answer 3

WEEK() actually works like WEEKOFYEAR(), not WEEKOFMONTH(), so it gives values 0 through 53 as the result, by default.

If you're saying that the first week of the month is the first 7 days, not starting at the first Sunday, then you can use DAYOFMONTH(), and BETWEEN to get your third week:

SELECT SUM(expense_details_amount) AS total 
  FROM expense_details
WHERE YEAR(expense_details_date) = 2010
  AND MONTH(expense_details_date) = 3
  AND DAYOFMONTH(expense_details_date) BETWEEN 15 AND 21
GROUP BY DAYOFMONTH(expense_details_date)

Notice that I also changed the constants to numerals instead of Strings so MySQL doesn't have to do the conversion.