title: MySQL AVG columns for Google Charts
body: <p>So, for a google chart I need my data in the following format:</p>
  
  <pre><code>+--------+---------+---------+---------+
  | Month  | Store A | Store B | Store C |
  +--------+---------+---------+---------+
  | May-19 |     9.5 |     9.4 |     8.8 |
  | Jun-19 |     7.9 |     9.4 |     7.2 |
  | Jul-19 |       9 |     9.3 |     8.8 |
  +--------+---------+---------+---------+
  
  </code></pre>
  
  <p>I'm struggling to format my SQL query to manage this though:</p>
  
  <pre class="lang-sql prettyprint-override"><code>    SELECT
      DATE_FORMAT(d.date_submitted,'%b-%y') AS 'Month',
      s.name as 'Store',
      FORMAT(AVG(d.q1),1) AS 'Satisfaction'
      FROM data d
      INNER JOIN store_list s ON d.store_list=s.store_id
      WHERE d.store_list IN (1,2,3)
      GROUP BY store_list, YEAR(d.date_submitted), MONTH(d.date_submitted)
      ORDER BY store_list,YEAR(d.date_submitted) ASC, MONTH(d.date_submitted) ASC
  </code></pre>
  
  <p>This gives me the data, but the layout is wrong.</p>
  
  <pre><code>+--------+---------+--------------+
  | Month  |  Store  | Satisfaction |
  +--------+---------+--------------+
  | May-19 | Store A |          9.5 |
  | Jun-19 | Store A |          7.9 |
  | Jul-19 | Store A |            9 |
  | May-19 | Store B |          9.4 |
  | Jun-19 | Store B |          9.4 |
  | Jul-19 | Store B |          9.3 |
  | May-19 | Store C |          8.8 |
  | Jun-19 | Store C |          7.2 |
  | Jul-19 | Store C |          8.8 |
  +--------+---------+--------------+
  
  
  </code></pre>
  
  <p>Bit of googling gave me this as an option, which is a layout google charts can use, but the data's completely wrong:</p>
  
  <pre class="lang-sql prettyprint-override"><code>    SELECT  
      DATE_FORMAT(date_submitted,'%b-%y') AS 'Month', 
      IF(store_list=1,FORMAT(AVG(q1),1),NULL) AS 'Store A',
      IF(store_list=2,FORMAT(AVG(q1),1),NULL) AS 'Store B',   
      IF(store_list=3,FORMAT(AVG(q1),1),NULL) AS 'Store C'
      FROM data
      GROUP BY YEAR(date_submitted), MONTH(date_submitted)    
      ORDER BY YEAR(date_submitted) ASC, MONTH(date_submitted) ASC
  </code></pre>
  
  <pre><code>+--------+---------+---------+---------+
  | Month  | Store A | Store B | Store C |
  +--------+---------+---------+---------+
  | NULL   | NULL    | NULL    | NULL    |
  | May-19 | 8.8     | NULL    | NULL    |
  | Jun-19 | 8.7     | NULL    | NULL    |
  | Jul-19 | NULL    | 9.2     | NULL    |
  +--------+---------+---------+---------+
  </code></pre>
  
  <p>I'm guessing IF's and AVG's don't play well together, but not sure how else I'm going to get my data into the right layout..</p>
  
  <p>[edit]</p>
  
  <p>For a single store I'm currently using:</p>
  
  <pre class="lang-php prettyprint-override"><code>&lt;?php 
      $sql = "SELECT COUNT(*) as Base, DATE_FORMAT(date_submitted,'%b-%y') AS 'Month', FORMAT(AVG(q1),1) AS 'Satisfaction' FROM data WHERE store_list IN (" . $user_stores . ") GROUP BY YEAR(date_submitted), MONTH(date_submitted) ORDER BY YEAR(date_submitted) ASC, MONTH(date_submitted) ASC";          
  
      $Sat = mysqli_query($conn,$sql);
      while($row = mysqli_fetch_array($Sat)){
          echo "['".$row['Month']."',".$row['Satisfaction'].",'Satisfaction: ".$row['Satisfaction']."\\nBase: ".$row['Base']."'],";
      }
  ?&gt;
  </code></pre>
  
  <p>Which works nicely with my Google Chart code, I don't know much PHP (picked that up on a google) so maybe there's another way I could feed the MySQL output into Google Charts - so I could return the array with the working code and then massage that array into what Google needs outside SQL?</p>