title: MySQL summing value across date range
body: <p>I have a table with each row containing a date and some arbitrary, numeric value. I need to sum this value for a specific but dynamic date interval.</p>
  
  <pre><code>SELECT VERSION();
  5.0.51a-24+lenny5
  
  CREATE TABLE IF NOT EXISTS `work_entries` (
    `entry_id` int(10) unsigned NOT NULL auto_increment,
    `employee_id` tinyint(3) unsigned NOT NULL,
    `work_date` date NOT NULL,
    `hour_count` tinyint(3) unsigned NOT NULL,
    PRIMARY KEY  (`entry_id`)
  );
  
  INSERT INTO `work_entries` (`entry_id`, `employee_id`, `work_date`, `hour_count`) VALUES
  (1, 1, '2011-04-25', 2),
  (2, 1, '2011-04-26', 3),
  (3, 1, '2011-04-27', 1),
  (4, 2, '2011-04-25', 2),
  (5, 2, '2011-04-27', 4),
  (6, 1, '2011-05-08', 2),
  (7, 2, '2011-05-06', 8),
  (8, 2, '2011-05-08', 9),
  (9, 2, '2011-05-09', 1),
  (10, 1, '2011-05-29', 3),
  (11, 1, '2011-05-30', 1),
  (12, 2, '2011-05-30', 2),
  (13, 1, '2011-06-02', 2),
  (14, 1, '2011-06-04', 3),
  (15, 1, '2011-06-14', 1),
  (16, 2, '2011-06-14', 2),
  (17, 2, '2011-06-17', 4),
  (18, 1, '2011-06-18', 2),
  (19, 2, '2011-06-19', 8),
  (20, 2, '2011-06-26', 9),
  (21, 2, '2011-07-01', 1),
  (22, 1, '2011-07-03', 3),
  (23, 1, '2011-07-03', 1),
  (24, 2, '2011-07-16', 2);
  </code></pre>
  
  <p>The following query returns the correct output for the above data set and should illustrate what I'm trying to do, however, I need to generate the year and month values based on <code>work_date</code>. The days (16, 15) never change. Specifically, this query will produce two rows, one for the specified interval and one for the rest, where I need one row for each period (16th of month N to 15th of month N+1 for all months with values).</p>
  
  <pre><code>SELECT
   SUM(hour_count) AS res
   FROM `work_entries`
   GROUP BY work_date
   BETWEEN '2011-04-16' AND '2011-05-15';
  
  -- Outputs
  res
  ----
  44
  32
  
  -- Should give
  res
  ----
  32
  14
  28
   2
  </code></pre>
  
  <p>An alternative example that works correctly but on the wrong interval (days 01-31):</p>
  
  <pre><code>SELECT
   SUM(hour_count) AS res
   FROM `work_entries`
   GROUP BY MONTH(work_date);
  
  -- Outputs
  res
  ---
  12
  26
  31
   7
  </code></pre>
  
  <p>Additionally, if there is a way to output the from- and to- dates at the same time I'd like that as well, but that's not very important.</p>