title: MYSQL hourly data inner join
body: <p>table user</p>
  
  <pre><code>username |   name   | role
  admin    | tony     | 1
  sales    | roy      | 2
  custo    | boy      | 3
  </code></pre>
  
  <p>table logstat</p>
  
  <pre><code>logdate             |   ip              | username
  2015/12/12 18:02:22 | 192.168.1.11      | admin
  2015/11/12 21:02:44 | 192.168.1.101     | sales
  2015/11/12 22:04:27 | 192.168.1.121     | custo
  </code></pre>
  
  <p>what I want the output is:</p>
  
  <pre><code>datetime              | admin   | sales     | custo
  (currentdate) 18:00:0 | 1       | 0         | 0
  (currentdate) 21:00:0 | 0       | 1         | 0
  (currentdate) 21:00:0 | 0       | 0         | 1
  </code></pre>
  
  <p>the output why I need (currentdate) to adjust with ARMCHART requierment when shows hourly data of each role in system.</p>
  
  <p>so far I got stuck on</p>
  
  <pre><code>SELECT CONCAT(Curdate(), ' ', HOUR(logdate)) as 'hourly', 
  (select count('logstat.username') 
     from logstat inner join user on user.username=logstat.username 
     and user.idrole='1' 
     where logdate BETWEEN (CURDATE() + INTERVAL (SELECT hourly(NOW())) hourly - INTERVAL 23 hourly) 
     AND ((CURDATE() + INTERVAL (SELECT hourly(NOW())) hourly)))as 'Admin' 
     FROM `logstat` GROUP BY `hourly`
  </code></pre>