title: How to calculate monthly average in MySQL given monthly data that spans more than one month?
body: <p>I have data that spans multiple months and I want to be able to take the average per day and separate it to the appropriate months.  For example, say that one data point is 2/9/2010 - 3/8/2010 and the amount is 1500.  Then, the query should return 1071.4 for February 2010 and 428.6 for March.  I am hoping there is a MySQL statement that will be able to do the computations instead of my PHP logic.  Thanks.</p>
  
  <p>EDIT (added table definition):
  start (datetime), end (datetime), use</p>
  
  <p>EDIT 2: Here is some dummy data</p>
  
  <pre><code>
  
  DROP TABLE IF EXISTS `dummy_data`;
  CREATE TABLE `dummy_data` (
    `id` int(11) NOT NULL AUTO_INCREMENT,
    `start_date` date NOT NULL,
    `end_date` date NOT NULL,
    `data` double(15,4) NOT NULL,
    PRIMARY KEY (`id`)
  ) ENGINE=MyISAM AUTO_INCREMENT=8 DEFAULT CHARSET=latin1;
  
  -- ----------------------------
  --  Records of `dummy_data`
  -- ----------------------------
  BEGIN;
  INSERT INTO `dummy_data` VALUES ('1', '2010-01-01', '2010-02-02', '200.0000'), ('2', '2010-02-03', '2010-02-25', '250.0000'), ('3', '2010-02-26', '2010-03-08', '300.0000'), ('4', '2010-03-09', '2010-04-12', '210.0000'), ('5', '2010-04-13', '2010-05-10', '260.0000'), ('6', '2010-05-11', '2010-06-15', '310.0000'), ('7', '2010-06-16', '2010-07-20', '320.0000');
  COMMIT;
  </code></pre>