SUM multiple date ranges in mysql query

Question

I have a MySql table 'Products' with three columns:

Date | Product | Qty

My aim is to SUM the qty of each product for every week.

Getting the SUM between two given dates would be easy:

SELECT SUM(qty) FROM products WHERE date >= '2010-01-01' AND date < '2011-01-1'

I can generate a list of weeks in Php using something like:

$today = date('Y-m-d', strtotime('last week Monday'));
$future = date('Y-m-d', strtotime('+90 days'));

$period = new DatePeriod(
 new DateTime($today),
 new DateInterval('P7D'),
 new DateTime($future)
);
foreach ($period as $key => $value) {
    $dates .= $value->format('Y-m-d');
}

Is there a MySql query that would work to Group By and SUM by dates? Or would I be better off looping through in Php?

Answer 1

• You can use Year() and Week() functions in MySQL, to get the year and week number for a given date. Week() function will return week number from 0 to 53. So, you will be needed to use Year() function alongwith, if you have data spanning over multiple years.
• But then, you will be more interested in knowing the start date and end date for the concerned week. This is where we can use a very interesting function DayOfWeek(). It returns the weekday index for a given date (1 = Sunday, 2 = Monday, …, 7 = Saturday)
• We can use Date_Add() function using the weekday index value and the actual date value, to determine the starting week date and ending week date for a given date.

Try the following (if the Week starts on Sunday) :

SELECT 
  DATE_ADD(`date`, INTERVAL(1 - DAYOFWEEK(`date`)) DAY) AS week_start_date, 
  DATE_ADD(`date`, INTERVAL(7 - DAYOFWEEK(`date`)) DAY) AS week_end_date, 
  SUM(qty) 
FROM 
  products 
GROUP BY week_start_date, week_end_date 

---

If the week starts on Monday, another handy function is WeekDay(). It returns the weekday index for date (0 = Monday, 1 = Tuesday, … 6 = Sunday).

Try the following (if the Week starts on Monday) :

SELECT 
  DATE_ADD(`date`, INTERVAL(0 - WEEKDAY(`date`)) DAY) AS week_start_date, 
  DATE_ADD(`date`, INTERVAL(6 - WEEKDAY(`date`)) DAY) AS week_end_date, 
  SUM(qty) 
FROM 
  products 
GROUP BY week_start_date, week_end_date 

Answer 2

You can achieve that with a group by like

GROUP BY week(date)