CODEWITHSUNDEEP
mysql
Ibn Saeed
Ibn Saeed

Reputation: 3301

MySQL Query to calculate the Previous Month

I would like to calculate 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.

Upvotes: 36

Views: 78259

Answers (7)

Vasanth Saminathan
Vasanth Saminathan

Reputation: 585

One more way to do this with:

DATE_ADD

... WHERE order_placed_date >= DATE_ADD(NOW(), INTERVAL -60 DAY) 
      AND order_placed_date <= DATE_ADD(NOW(), INTERVAL -30 DAY)

Upvotes: -1

Artem Russakovskii
Artem Russakovskii

Reputation: 22023

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 00:00:00')
AND DATE_FORMAT(LAST_DAY(NOW() - INTERVAL 1 MONTH), '%Y-%m-%d 23:59:59')

Upvotes: 77

BigBadMe
BigBadMe

Reputation: 1852

Simplest solution:

SELECT SUM(goods_total) AS Total_Amount FROM orders
WHERE YEAR(order_placed_date) = YEAR(CURDATE() - INTERVAL 1 MONTH)
AND MONTH(order_placed_date) = MONTH(CURDATE() - INTERVAL 1 MONTH)

Upvotes: 12

fubo
fubo

Reputation: 46005

here another solution - without stringoperation

SELECT SUM(goods_total) AS Total_Amount  FROM orders
WHERE  DATE(order_placed_date) 
BETWEEN DATE_ADD(LAST_DAY(DATE_SUB(NOW(), INTERVAL 2 MONTH)), INTERVAL 1 DAY) 
AND LAST_DAY(DATE_SUB(NOW(), INTERVAL 1 MONTH));

Upvotes: 2

Shane
Shane

Reputation: 697

I found that Artem's query wasn't returning anything from the last day of the previous month(presumably as BETWEEN would calculate from time 00:00:00 of the last day).

This seems to work for me for the previous month.

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

Upvotes: 9

Ibn Saeed
Ibn Saeed

Reputation: 3301

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.

Upvotes: 1

knopr
knopr

Reputation:

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.

Upvotes: 0

Related Questions