MySQL Query to calculate the Previous Month

Question

Hello

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

I got the query for getting the data for the present month from the current date.

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

Now how can I get Previous Months Data only, excluding this month.

For e.g. This month (July) I made $15,000 and last Month(June) i made $14,000.

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

But i dont know how to calculate Previous Months.

Answer 1

Here you go, use this to get the date between the 1st of last month and the last of last month in MySQL:

... order_placed_date BETWEEN date_format(NOW() - INTERVAL 1 MONTH, '%Y-%m-01') AND last_day(NOW() - INTERVAL 1 MONTH)

Answer 2

If you are lazy it's kinda convenient to use

...date_format(order_placed_date, '%Y-%m') = date_format(now() - INTERVAL 1 MONTH, '%Y-%m')

I think it also increases readability.

Answer 3

Here is another way i found:

SELECT SUM(goods_total) AS Total_Amount  FROM orders
WHERE SUBSTRING(o.order_placed_date FROM 1 FOR 7) =   SUBSTRING(CURRENT_DATE - INTERVAL 1 MONTH FROM 1 FOR 7)

This works as well.