MySQL query to calculate the previous week

Question

I would like to calculate the total order amount for the previous week.

I received a request for data for the last 7 days from the current date.

SELECT SUM(goods_total) AS Total_Amount  FROM orders
WHERE order_placed_date >= date_sub(current_date, INTERVAL 7 day);

Now, how can I get data for the previous week, except for this week.

For example, this week I made $ 15,000, and last week I made $ 14,000.

I get $ 15,000 by completing the above request.

But I do not know how to calculate the previous week.

Answer 1

Based on the additional information you gave Alex in response, I think the following might work:

SELECT SUM(goods_total) AS Total_Amount, "Previous week" AS Week FROM orders
WHERE order_placed_date >= date_sub(current_date, INTERVAL 14 day) 
AND order_placed_date < date_sub(current_date, INTERVAL 7 day)
UNION
SELECT SUM(goods_total) AS Total_Amount, "This week" AS Week FROM orders
WHERE order_placed_date >= date_sub(current_date, INTERVAL 7 day) 

This should return two rows and two columns with a total for each of the weeks.

Answer 2

8-14 , 7 , :

SELECT * FROM `Table_Name` 
WHERE `Field_Name` >= DATE_SUB(NOW(),INTERVAL 14 DAY) 
AND `Field_Name` < DATE_SUB(NOW(),INTERVAL 7 DAY)

• Field_Name: YYYY-mm-dd (2012-09-20).
• Table_Name: .

Answer 3

" " ( 7- ) , ( ),

SELECT SUM(goods_total) AS Total_Amount FROM orders
WHERE order_placed_date BETWEEN
 date_sub(current_date, INTERVAL 14 day) AND
 date_sub(current_date, INTERVAL 8 day);

Answer 4

DATE_SUB().

Answer 5

Try:

SELECT SUM(goods_total) AS Total_Amount  FROM orders
WHERE order_placed_date >= date_sub(current_date, INTERVAL 14 day) 
AND order_placed_date < date_sub(current_date, INTERVAL 7 day);

Answer 6

:

SELECT   count(order_placed_date) 
FROM     orders
WHERE    YEARWEEK(order_placed_date) = YEARWEEK(CURRENT_DATE - INTERVAL 7 DAY) 

Answer 7

mysql> SELECT DATE_SUB(current_date, INTERVAL WEEKDAY(current_date)+8 DAY) last_sunday
, YEARWEEK(DATE_SUB(current_date, INTERVAL WEEKDAY(current_date)+8 DAY)) last_week_of_year
, DATE_SUB(current_date, INTERVAL WEEKDAY(current_date)+1 DAY) this_sunday
, YEARWEEK(DATE_SUB(current_date, INTERVAL WEEKDAY(current_date)+1 DAY)) this_week_of_year;

*************************** 1. row ***************************
      last_sunday: 2012-01-29
last_week_of_year: 201205
      this_sunday: 2012-02-05
this_week_of_year: 201206

Answer 8

The easiest way to do this is to use mysql Date and Time Functions

WEEKOFYEAR ()