title: MySQL grouping totals by multiple date and results using case
body: <p>I have a results table which lists a set of values, each linking to another table containing the date that result was made.</p>
  
  <p>I have working SQL to get all the dates (using CASE's) however I can only retrieve a single range of results.</p>
  
  <pre><code>    Select 
      count(CASE
      WHEN  results.test_id IN ( SELECT id 
      FROM  `test` 
      WHERE  `posted` 
      BETWEEN  '2011-07-01 00:00:00'
      AND  '2011-07-01 23:59:59') 
      THEN results.test_id
      ELSE NULL
          END) AS "1st July"          
      from `results`
      WHERE results.window_id = 2 and results.mark &gt; 90;
  </code></pre>
  
  <p>I also have another SQL query which gets all the ranges but can only work for one date at a time.</p>
  
  <pre><code>SELECT
  CASE
      when mark &gt; 90 then '&gt;90%'
      when mark &gt; 80 then '&gt;80%'
      when mark &gt; 70 then '&gt;70%'
  END as mark_results,
  COUNT(*) AS count
  
  FROM (SELECT mark from results where window_id =2) as derived
  GROUP BY mark_results
  ORDER BY mark_results;
  </code></pre>
  
  <p>What I'd like is to have everything in one unified query, displaying the relevant totals for each range of results. such as below:</p>
  
  <pre><code>Result Range | 1st July | 2nd July | 3rd July | 4th July
  &gt;90%         |    0     |    0     |    0     |    1
  &gt;80%         |    1     |    2     |    1     |    1
  &gt;70%         |    4     |    5     |    5     |    4
  </code></pre>
  
  <p>So that the totals for each range are displayed under their date.</p>
  
  <p>I assume it's possible.</p>