Adding blank rows to display of result set returned by MySQL query

I am storing hourly results in a MySQL database table which take the form:

ResultId,CreatedDateTime,Keyword,Frequency,PositiveResult,NegativeResult 
349,2015-07-17 00:00:00,Homer Simpson,0.0,0.0,0.0
349,2015-07-17 01:00:00,Homer Simpson,3.0,4.0,-2.0
349,2015-07-17 01:00:00,Homer Simpson,1.0,1.0,-1.0
349,2015-07-17 04:00:00,Homer Simpson,1.0,1.0,0.0
349,2015-07-17 05:00:00,Homer Simpson,8.0,3.0,-2.0
349,2015-07-17 05:00:00,Homer Simpson,1.0,0.0,0.0

Where there might be several results for a given hour, but none for certain hours.

If I want to produce averages of the hourly results, I can do something like this:

SELECT ItemCreatedDateTime AS 'Created on', 
KeywordText AS 'Keyword', ROUND(AVG(KeywordFrequency), 2) AS 'Average frequency', 
ROUND(AVG(PositiveResult), 2) AS 'Average positive result',     
ROUND(AVG(NegativeResult), 2) AS 'Average negative result' 
FROM Results
WHERE ResultsNo = 349 AND CreatedDateTime BETWEEN '2015-07-13 00:00:00' AND '2015-07-19 23:59:00' 
GROUP BY KeywordText, CreatedDateTime
ORDER BY KeywordText, CreatedDateTime

However, the results only include the hours where data exists, e.g.:

349,2015-07-17 01:00:00,Homer Simpson,2.0,2.5,-1.5    
349,2015-07-17 04:00:00,Homer Simpson,1.0,1.0,0.0
349,2015-07-17 05:00:00,Homer Simpson,4.5,1.5,-1.0

But I need to show blanks rows for the missing hours, e.g.

349,2015-07-17 01:00:00,Homer Simpson,2.0,2.5,-1.5   
349,2015-07-17 02:00:00,Homer Simpson,0.0,0.0,0.0
349,2015-07-17 03:00:00,Homer Simpson,0.0,0.0,0.0
349,2015-07-17 04:00:00,Homer Simpson,1.0,1.0,0.0
349,2015-07-17 05:00:00,Homer Simpson,4.5,1.5,-1.0

Short of inserting blanks into the results before they are presented, I am uncertain of how to proceed: can I use MySQL to include the blank rows at all?

Solution

SQL in general has no knowledge about the data, so you have to add that yourself. In this case you will have to insert the not used hours somehow. This can be done by inserting empty rows, or a bit different by counting the hours and adjusting your average for that.

Counting the hours and adjusting the average:

Count all hours with data (A)
Calculate the number of hours in the period (B)
Calculate the avg as you already did, multiply by A divide by B

Example code to get the hours:

SELECT COUNT(*) AS number_of_records_with_data, 
(TO_SECONDS('2015-07-19 23:59:00')-TO_SECONDS('2015-07-13 00:00:00'))/3600 
  AS number_of_hours_in_interval
FROM Results
WHERE ResultsNo = 349 AND CreatedDateTime 
  BETWEEN '2015-07-13 00:00:00' AND '2015-07-19 23:59:00' 
GROUP BY KeywordText, CreatedDateTime;

And just integrate it with the rest of your query.