f8hXr25B

1selecting between date range and forcing empty rows to be 0?
2expires    | value  
3-------------------
42011-06-15 | 15  
52011-06-15 | 15  
62011-06-25 | 15  
72011-07-15 | 15  
82011-07-15 | 15  
92011-07-25 | 15  
102011-08-15 | 15  
112011-08-15 | 15  
122011-08-25 | 15
13	
14June   | 45  
15July   | 45  
16August | 45
17	
18SELECT SUM(amount) AS `amount`, 
19         DATE_FORMAT(expires , '%M') AS `month`  
20    FROM dealDollars 
21   WHERE DATE(expires) BETWEEN DATE(NOW()) 
22                           AND LAST_DAY(DATE(NOW()+INTERVAL 3 MONTH)) 
23GROUP BY MONTH(expires)
24	
25SELECT NVL(B.amount, 0) as `amount`, A.month as `month`
26      FROM (SELECT 'January' as `month`
27            UNION SELECT 'February' as `month`
28            UNION SELECT 'March' as `month`...
29            UNION SELECT 'DECEMBER' as `month`) as A
30      LEFT JOIN
31            (SELECT SUM(amount) AS `amount`, 
32                    DATE_FORMAT(expires , '%M') AS `month`  
33               FROM dealDollars 
34               WHERE 
35                  DATE(expires) BETWEEN 
36                       DATE(NOW()) AND 
37                       LAST_DAY(DATE(NOW()+INTERVAL 3 MONTH)) 
38               GROUP BY MONTH(expires)) as B
39            ON (A.MONTH = B.MONTH)
40	
41DROP TABLE IF EXISTS `example`.`numbers`;
42CREATE TABLE  `example`.`numbers` (
43  `id` int(10) unsigned NOT NULL auto_increment,
44   PRIMARY KEY  (`id`)
45) ENGINE=InnoDB DEFAULT CHARSET=latin1;
46	
47INSERT INTO NUMBERS
48  (id)
49VALUES
50  (NULL)
51	
52SELECT x.dt
53  FROM (SELECT DATE(DATE_SUB(CURRENT_DATE(), INTERVAL (n.id - 1) MONTH)) AS dt
54          FROM numbers n
55         WHERE DATE(DATE_SUB(CURRENT_DATE(), INTERVAL (n.id - 1) MONTH)) BETWEEN CURRENT_DATE()
56                                                                             AND LAST_DAY(CURRENT_DATE() +INTERVAL 3 MONTH))  ) x
57	
58SELECT x.dt,
59          COUNT(*) AS total
60     FROM (SELECT DATE(DATE_SUB(CURRENT_DATE(), INTERVAL (n.id - 1) MONTH)) AS dt
61          FROM numbers n
62         WHERE DATE(DATE_SUB(CURRENT_DATE(), INTERVAL (n.id - 1) MONTH)) BETWEEN CURRENT_DATE()
63                                                                             AND LAST_DAY(CURRENT_DATE() +INTERVAL 3 MONTH))  ) x
64LEFT JOIN YOUR_TABLE y ON y.date = x.dt
65 GROUP BY x.dt
66 ORDER BY x.dt