title: Get the hourly average amount based on rows with minutely timestamp
body: <p>I have a table structure that looks like this:</p>
  
  <pre><code>+---------+------------+----------+---------+-----------+------------+
  | id      | account_id | hashrate | workers | sharerate | timestamp  |
  +---------+------------+----------+---------+-----------+------------+
  | 1227368 |         42 |   405211 |       1 |      6183 | 1534264380 |
  | 1227367 |         12 |   450077 |       1 |      6868 | 1534264380 |
  +---------+------------+----------+---------+-----------+------------+
  </code></pre>
  
  <p>A row is created every minute for a user with an active worker. </p>
  
  <p>I'm attempting to display a chart that will show the average hashrate and sharerate for each hour over a 24 hour period. </p>
  
  <p>My sql query so far is:</p>
  
  <pre><code>  SELECT 
      timestamp,
      SUM(hashrate) as hashes,
      SUM(sharerate) AS shares
    FROM statistics_users
    WHERE FROM_UNIXTIME(timestamp) &gt;= DATE_SUB(NOW(), INTERVAL 1 DAY)
    GROUP BY timestamp;
  </code></pre>
  
  <p>which returns: </p>
  
  <pre><code>+------------+------------+----------+
  | timestamp  | hashes     | shares   |
  +------------+------------+----------+
  | 1534177980 | 2282744244 | 34831913 |
  </code></pre>
  
  <p>Which is returning 1440 objects (one for each minute of day). I want to group the items by hour and average all sums returned from my query of minutes into an hour. Not sure where to go from here and I'm fairly new to writing sql queries, so any help would be much appreciated.</p>