title: mysql grouping by week
body: <p>I have a table with the following fields:</p>
  
  <ul>
  <li>id</li>
  <li>amount_sale</li>
  <li>the_date (unix timestamp integer)</li>
  <li>payment_type (can be Cash, or Account)</li>
  </ul>
  
  <p>I am trying to create a query that will group all sales by each week of the year, and then split the sum of amount_sales for each week on my page.</p>
  
  <p>Example:</p>
  
  <pre><code>week 1 = $26.00  
  week 2 = $35.00  
  week 3 = $49.00
  </code></pre>
  
  <p>etc.  I'm using this query but it's not working:</p>
  
  <pre><code>  SELECT SUM(`amount_sale`) as total 
      FROM `sales` 
     WHERE `payment_type` = 'Account' 
  GROUP BY WEEK(`the_date`)
  </code></pre>