title: SQL Query count rows where multiple column-values
body: <p>So this is a how my MySQL table looks like (I have many thousands of rows):</p>
  <pre><code>| ID |         date        |   Color |   IUSERQ | 
  |  1 | 2020-09-25 18:55:54 |    RED  |    GGW3  |
  |  2 | 2020-09-25 18:24:12 |    RED  |    FFQ3  |
  |  3 | 2020-09-24 17:32:52 |    RED  |    GWW3  |
  |  4 | 2020-09-23 17:42:37 |   BLUE  |    JJN6  |
  |  5 | 2020-09-23 17:33:55 |   BLUE  |    VVV5  |
  |  6 | 2020-09-23 18:53:57 |    RED  |    FFQ3  |
  |  7 | 2020-09-22 18:15:11 |   BLUE  |    FFQ3  |
  </code></pre>
  <p>Now to count all of the rows and group them in weeks, I do this:</p>
  <pre><code>if($stmt = $link-&gt;query(&quot;SELECT WEEK(date),COUNT(*) FROM sales WHERE color='RED' AND YEAR(date) = YEAR(NOW()) GROUP BY WEEK(date) order by MONTH(date) ASC&quot;)){
  
  $php_data_array = Array(); // create PHP array
  while ($row = $stmt-&gt;fetch_row()) {
     $php_data_array[] = $row; // Adding to array
     }
  }else{
  echo $link-&gt;error;
  }
  
  echo json_encode($php_data_array); 
  </code></pre>
  <p>On the <code>echo json_encode($php_data_array);</code>, it gives the this current output: <code>[[&quot;36&quot;,&quot;154&quot;],[&quot;37&quot;,&quot;247&quot;],[&quot;38&quot;,&quot;275&quot;]]</code>. So the first string in the array (36, 37, 38) is the week number, and the second one the number of rows where color is RED. Now, I also want to add where color is BLUE in the same array, so the expected value should be something like: <code>[[&quot;36&quot;,&quot;154&quot;,&quot;166&quot;],[&quot;37&quot;,&quot;247&quot;,&quot;265&quot;],[&quot;38&quot;,&quot;275&quot;,&quot;298&quot;]]</code>.</p>
  <p>What approach should I use to do this?</p>