mysql grouping by week

Question

I have a table with the following fields:

• id
• amount_sale
• the_date (unix timestamp integer)
• payment_type (can be Cash, or Account)

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.

Example:

week 1 = $26.00  
week 2 = $35.00  
week 3 = $49.00

etc. I'm using this query but it's not working:

  SELECT SUM(`amount_sale`) as total 
    FROM `sales` 
   WHERE `payment_type` = 'Account' 
GROUP BY WEEK(`the_date`)

Answer 1

If you store the_date as integer, you first need to convert it to datetime using FROM_UNIXTIME function:

 SELECT SUM(`amount_sale`) as total 
FROM `sales` 
WHERE `payment_type` = 'Account' 
GROUP BY WEEK(FROM_UNIXTIME(`the_date`))  

UPDATE:
Also, you might want to output week number,

SELECT CONCAT('Week ', WEEK(FROM_UNIXTIME(`the_date`))) as week_number,
SUM(`amount_sale`) as total 
FROM `sales` 
WHERE `payment_type` = 'Account' 
GROUP BY WEEK(FROM_UNIXTIME(`the_date`))

Answer 2

Try to also select the weeks in your query, like this:

  SELECT SUM(`amount_sale`) as total, WEEK(`the_date`) as week
  FROM `sales` 
  WHERE `payment_type` = 'Account' 
  GROUP BY week ORDER BY week ASC

If you have weeks covering several years you could also select the year from the_date and order on that as well, like

  ORDER BY week ASC, year ASC