MySQL Query to calculate Previous Week

Question

Hello

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

I got the query for getting the 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 Previous week data only, excluding this week.

For e.g. This week I made $15,000 and last week i made $14,000.

I get the $15,000 by running the above query.

But i dont know how to calculate previous week.

Answer 1

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

WEEKOFYEAR()

Answer 2

You can use DATE_SUB().

Answer 3

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 4

If you want "rolling weeks" (sliding 7-days-long windows) instead of weeks starting on Sunday (or other specific weekdays),

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 5

What about the following query:

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

Answer 6

Based on the additional information you gave in Alex's answer, I guess the following may 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 amount for each of the weeks.