title: SQL for group by week starting Wednesday
body: <p>I have a report I want to produce which displays the count of jobs for each week of each year, with dates going back to the early 90s. I have run into a problem I can't seem to solve though as my SQL knowledge isn't that great.</p>
  
  <p>I understand mysql limits you to starting the week dates as either Sunday or Monday. I would like to group them from Wednesday to Tuesday, so when I do the <code>GROUP BY YEAR(JOBID), WEEK(JOBID)</code> it only displays them grouped into weeks starting on Sunday.</p>
  
  <p>I found this on here</p>
  
  <pre><code>SELECT
      count(jobs) as count,
      CASE WHEN (weekday(datetime)&lt;=3) THEN date(datetime + INTERVAL (3-weekday(datetime)) DAY)
          ELSE date(datetime + INTERVAL (3+7-weekday(datetime)) DAY)
      END as dt
  FROM values
  </code></pre>
  
  <p>but it gives me the same result set as </p>
  
  <pre><code>SELECT 
      COUNT(jobs), 
      Week(datetime) as Week, 
      Year(datetime) as Year 
  FROM jobs 
  GROUP BY Year(datetime), Week(datetime)
  </code></pre>
  
  <p>Can someone please point out what I'm doing wrong?</p>