列出没有数据库数据的月份

Question

The current query work perfect the problem is there is no data for the current month (April) so it does not list the month as 0, How do I get it to list the month as having 0 interivews?

QUERY

select distinct from_unixtime(interv_date,'%M %Y') AS month, count(*) as totals
from " . TABLE_PREFIX . "interviews
group by month
order by interv_date desc
limit 6

NOTE that the interv_date column is a epoch timestamp (example: 1428003691)

PHP

<table class=\"highchart\" data-graph-container=\"#graphcontainer\" data-graph-type=\"column\" data-graph-legend-disabled=\"1\">
<caption>Interviews by Month</caption>
<thead>
<tr>
<th>Month</th>
<th>Totals</th>
</tr>
</thead>
<tbody>
<?php
for ($i = 0; $i < $stat_logs["count"]; $i++) {
    echo "<tr>
";
    echo "<td>" . $stat_logs[$i]["month"] . "</td>
";
    echo "<td>" . $stat_logs[$i]["totals"] . "</td>
";
    echo "</tr>
";
}
?>
echo "</tbody>
echo "</table>

OUTPUT

Month           Totals
March 2015      7
February 2015   5
January 2015    12
December 2014   18
November 2014   19
October 2014    5

I need it to output something like this:

Month           Totals
April 2015      0
March 2015      7
February 2015   5
January 2015    12
December 2014   18
November 2014   19

Take not that not every month will have interviews so it won't just be the current month (first month) Any help on how I can get the months with no interviews to show up on the list would be great.

Answer 1

I've been working on this WAAAAAYYYY too long. But here's a start... http://sqlfiddle.com/#!9/23389/2

It works perfectly well but it's a bit clunky, with two temp tables. No hardcoding dates, though! The tables should actually be temp tables, although sqlfiddle didn't work with temp tables so I made them real tables.

-- Get a table with the months of interest in it
SET @RightNow = curdate();
create table LastSixMonths (dateVal datetime);
Insert into LastSixMonths values(@RightNow);
Insert into LastSixMonths values(@RightNow - INTERVAL 1 MONTH);
Insert into LastSixMonths values(@RightNow - INTERVAL 2 MONTH);
Insert into LastSixMonths values(@RightNow - INTERVAL 3 MONTH);
Insert into LastSixMonths values(@RightNow - INTERVAL 4 MONTH);
Insert into LastSixMonths values(@RightNow - INTERVAL 5 MONTH);

-- Summarize the recent interviews
create table Totals (monthAndYear varchar (20), interviews int);
Insert into Totals
select distinct from_unixtime(intv.interv_date,'%M %Y') as monthAndYear, 
                count(*) 
from interviews intv 
group by from_unixtime(intv.interv_date,'%M %Y')
order by interv_date desc
limit 6

-- Do a left join on recent months and summarized interviews
select date_format(six.dateval, '%M %Y'), 
    IF (interviews IS NULL, 0, Interviews) as totalInterviews
from LastSixMonths six 
left join Totals tot 
    on date_format(six.dateval, '%M %Y') = tot.monthAndYear
order by six.dateval desc
limit 6;