How to return zero values if nothing was written in time interval?
<p>I am using the Graph Reports for the select below. The MySQL database only has the active records in the database, so if no records are in the database from X hours till Y hours that select does not return anything. So in my case, I need that select return Paypal zero values as well even the no activity was in the database. And I do not understand how to use the <code>UNION</code> function or re-create select in order to get the zero values if nothing was recorded in the database in time interval. Could you please help?</p>

<pre><code>select STR_TO_DATE ( DATE_FORMAT(`acctstarttime`,'%y-%m-%d %H'),'%y-%m-%d %H') 
as '#date', count(*) as `Active Paid Accounts`  
from radacct_history where `paymentmethod` = 'PayPal' 
group by DATE_FORMAT(`#date`,'%y-%m-%d %H')
</code></pre>

<p>When I run the select the output is:</p>

<p><a href="http://i.stack.imgur.com/lMYbB.jpg" rel="nofollow">Current Output</a></p>

<p>But I need if there are no values between 2016-07-27 07:00:00 and 2016-07-28 11:00:00, then in every hour it should show zero active accounts Like that:</p>

<p><a href="http://i.stack.imgur.com/QEvMI.jpg" rel="nofollow">Needed output with no values every hour</a></p>

<p>I have created such select below , but it not put to every hour the zero value like i need. showing the big gap between the 12 Sep and 13 Sep anyway, but there should be the zero values every hour </p>

<p>(select STR_TO_DATE ( DATE_FORMAT(<code>acctstarttime</code>,'%y-%m-%d %H'),'%y-%m-%d %H') 
as '#date', count(paymentmethod) as <code>Active Paid Accounts</code><br>
from radacct_history where <code>paymentmethod</code> &lt;> 'PayPal' 
group by DATE_FORMAT(<code>#date</code>,'%y-%m-%d %H'))
union ALL
(select STR_TO_DATE ( DATE_FORMAT(<code>acctstarttime</code>,'%y-%m-%d %H'),'%y-%m-%d %H') 
as '#date',  0 as <code>Active Paid Accounts</code><br>
from radacct_history where <code>paymentmethod</code> &lt;> 'PayPal' 
group by DATE_FORMAT(<code>#date</code>,'%y-%m-%d %H')) ;</p>