MySQL AVG columns for Google Charts

Question

So, for a google chart I need my data in the following format:

+--------+---------+---------+---------+
| 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 |
+--------+---------+---------+---------+

I'm struggling to format my SQL query to manage this though:

    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

This gives me the data, but the layout is wrong.

+--------+---------+--------------+
| 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 |
+--------+---------+--------------+

Bit of googling gave me this as an option, which is a layout google charts can use, but the data's completely wrong:

    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

+--------+---------+---------+---------+
| 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    |
+--------+---------+---------+---------+

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..

[edit]

For a single store I'm currently using:

<?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']."'],";
    }
?>

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?

score 1 · Accepted Answer · answered Jul 11 '19 at 06:18

Ah, my IF's were on the outside of the AVG not the inside:

SELECT
    DATE_FORMAT(date_submitted,'%b-%y') AS 'Month',
    FORMAT(AVG(CASE WHEN store_list = 1 THEN q1 ELSE NULL END),1) AS 'Store A',
    FORMAT(AVG(CASE WHEN store_list = 2 THEN q1 ELSE NULL END),1) AS 'Store B',
    FORMAT(AVG(CASE WHEN store_list = 3 THEN q1 ELSE NULL END),1) AS 'Store C'
FROM data
WHERE store_list IN (1,2,3)
GROUP BY YEAR(date_submitted), MONTH(date_submitted)
ORDER BY YEAR(date_submitted) ASC, MONTH(date_submitted) ASC

This gives me:

+--------+---------+---------+---------+
| 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|
+--------+---------+---------+---------+

Now to work out how to do bases and dynamically insert the relevant store at each point AND to do it for N stores...never ends does it - hope this helps someone!

MySQL AVG columns for Google Charts

1 Answers1