title: How to parse data from a mysql tracking db?
body: <p>I have a working event tracker that writes every event in a mysql table.</p>
  
  <p>Table</p>
  
  <pre><code>id      date                userid      event
  5100    2014-03-25 14:18:55 user333     AI  
  5101    2014-03-25 14:19:02 user333     Click
  5102    2014-03-25 14:19:02 user333     Click
  ...
  </code></pre>
  
  <p>Thats works so far very good. But now, I want to write a little report tool in node.js</p>
  
  <p>I try to get the values with this SQL Query:</p>
  
  <blockquote>
    <p>SELECT DATE_FORMAT(date,"%Y%m%d") AS date, event, count(*) AS count FROM databasetest WHERE date>="'+ daystartdate +'" AND date&lt;="'+ dayenddate +'" GROUP BY YEAR(date), MONTH(date), DAY(date), event</p>
  </blockquote>
  
  <p>Giving me this:</p>
  
  <pre><code>+----------+----------+-------+
  | date     | event    | count |
  +----------+----------+-------+
  | 20140320 | AI       |     6 |
  | 20140320 | Click    |     2 |
  | 20140320 | swipe    |     2 |
  | 20140321 | Click    |     6 |
  | 20140321 | error    |     5 |
  | 20140321 | swipe    |     2 |
  | 20140321 | touch    |     3 |
  | 20140322 | AI       |     3 |
  | 20140322 | Click    |     3 |
  | 20140322 | error    |     1 |
  | 20140322 | mapsload |     3 |
  | 20140322 | touch    |     1 |
  | 20140323 | AI       |     2 |
  | 20140323 | Click    |     2 |
  | 20140323 | touch    |     5 |
  | 20140324 | AI       |     3 |
  | 20140324 | Click    |     1 |
  | 20140325 | AI       |    25 |
  | 20140325 | Click    |    48 |
  | 20140325 | error    |    16 |
  | 20140325 | mapsload |     7 |
  | 20140325 | swipe    |    15 |
  | 20140325 | touch    |    32 |
  +----------+----------+-------+
  </code></pre>
  
  <p>But I need the data in this form:</p>
  
  <pre><code>+----------+----------+-------+-----
  | date     | Click    | AI    | ....
  +----------+----------+-------+-----
  | 20140320 | 0        |     6 |
  | 20140321 | 2        |     2 |
  | 20140321 | 2        |     5 |
  </code></pre>
  
  <p>Is this possible with a SQL query or do I need to loop through the results in my javascript code. I tried many possible solution but didnt get it to work. </p>
  
  <p>Thank you.</p>