MySQL Query : Select existing months from database and complete dynamically with the rest of year's month

Question

Hi all,

I have two tables:

CREATE TABLE 'sales_sheet' (
  `_id` int(11) NOT NULL AUTO_INCREMENT,
  `_typed_by` int(11) DEFAULT NULL,
  `_product_id` int(11) DEFAULT NULL,
  `_year` date DEFAULT NULL,
  `_validation_state` int(11) DEFAULT NULL,
  PRIMARY KEY  (`_id`),
  KEY `_product_id` (`_product_id`)
) ENGINE=MyISAM DEFAULT CHARSET=latin1;

CREATE TABLE 'sales_sheet_entries' (
  `_id` int(11) NOT NULL AUTO_INCREMENT,
  `_sheet_id` int(11) DEFAULT NULL,
  `_month` date DEFAULT NULL,
  `_quantity` int(11) DEFAULT NULL,
  PRIMARY KEY  (`_id`)
) ENGINE=MyISAM DEFAULT CHARSET=latin1;

These two tables are used to store sheets of annual sales. The first table stores the product identifier, the year and the validation status of the sheet. The second stores the monthly sales for a product.

I want, in a single query get this result :

---------------------------------------------
| Month         | Sales volume  |
---------------------------------------------
| January 2010      |       |
| February 2010     | XXXXXX    |
| March 2010        | XXXXXX    |
| April 2010        | XXXXXX    |
| May 2010          |       |
| June 2010         |       |
| July 2010         |       |
| August 2010       |       |
| September 2010        |       |
| October 2010      | XXXXXX    |
| November 2010     | XXXXXX    |
| December 2010     |       |
---------------------------------------------

Empty fields in the sales volume column may correspond to a record already saved in the sales_sheet_entries table but has no corresponding sales volume or simply non-existent on the database, but the query must display it.

One of my constraints is that I can not put 12 columns correspond directly to the list of months in the sales_sheet table because the client specifications are changing and he may demand that sales volume will be filled by period instead of month.

I hope I was clear enough, thank you in advance for your help & sorry for my english.

Answer 1

MySQL doesn't have recursive functionality, so you're left with using the NUMBERS table trick -

1. Create a table that only holds incrementing numbers - easy to do using an auto_increment:

   DROP TABLE IF EXISTS `example`.`numbers`;
   CREATE TABLE  `example`.`numbers` (
     `id` int(10) unsigned NOT NULL auto_increment,
      PRIMARY KEY  (`id`)
   ) ENGINE=InnoDB DEFAULT CHARSET=latin1;
   

2. Populate the table using:

   INSERT INTO NUMBERS
     (id)
   VALUES
     (NULL)
   

   ...for as many values as you need.

3. Use DATE_ADD to construct a list of times, increasing the months based on the NUMBERS.id value:

   SELECT x.*
     FROM (SELECT DATE_ADD('2010-01-01', INTERVAL n.id - 1 MONTH)
             FROM numbers n) x
   

4. LEFT JOIN onto your table of data based on the datetime portion:

      SELECT DATE_FORMAT(x.dt, '%M %Y') AS Month,
             y.sales_volume
        FROM (SELECT DATE_ADD('2010-01-01', INTERVAL n.id - 1 MONTH) AS dt
                FROM numbers n) x
   LEFT JOIN (SELECT ss._product_id,
                     ss._year,
                     sse._month,
                     SUM(sse._quantity) AS sales_volume
                FROM SALES_SHEET ss
                JOIN SALES_SHEET_ENTRIES sse ON sse._sheet_id = ss._id
             GROUP BY ss._product_id, ss._year, sse._month) y ON y._month = MONTH(x.dt)
                                                             AND y._year = YEAR(x.dt)
                                                             AND y._product_id = ?