Last updated on June 2nd, 2020 at 09:17 am
Sometimes, you may need to get last 12 months sales data for reporting. Here’s how to get last 12 months’ records from datetime column. You can use it to get last 12 months data in MySQL, get number of users signed up in last 12 months, and get last 1 year’s sales data for further analysis.
How To Get Last 12 Months Data in MySQL
Here are the steps to get last 12 months data in MySQL. Let’s say you have the following table sales(order_date, sale, orders) that contains daily number of orders and sale amount.
mysql> select * from sales; +------------+------+--------+ | order_date | sale | orders | +------------+------+--------+ | 2020-04-28 | 300 | 10 | | 2020-04-29 | 250 | 15 | | 2020-04-30 | 250 | 12 | | 2020-05-01 | 250 | 14 | | 2020-05-02 | 150 | 20 | | 2020-05-03 | 300 | 21 | | 2020-05-04 | 200 | 15 | | 2020-05-05 | 200 | 17 | | 2020-05-06 | 250 | 12 | | 2020-05-07 | 150 | 15 | | 2020-05-08 | 300 | 12 | | 2020-05-09 | 200 | 18 | | ... | ... | ... | +------------+------+--------+
Bonus Read : How to Create View in MySQL
How to Get Last 12 Months Sales Data in SQL
Here’s the SQL query to get last 12 months data in MySQL
mysql> select * from sales
where order_date> now() - INTERVAL 12 month;
In the above query, we use system function now() to get current datetime. Then we use INTERVAL clause to filter those records where order_date falls after an interval of 12 months before present datetime.
Bonus Read : How to Get New Users Per Day in MySQL
How to Get Sign ups & New Users in Current Month in MySQL
Similarly, you can also get new sign ups & users in last 12 months in MySQL. Let’s say you have the following table Users(user_id, date_joined) that contains signup data.
mysql> select * from users; +-------------+---------+ | date_joined | user_id | +-------------+---------+ | 2020-04-28 | 213 | | 2020-04-28 | 214 | | 2020-04-29 | 215 | | 2020-04-29 | 216 | | 2020-04-29 | 217 | | 2020-04-30 | 218 | | 2020-04-30 | 219 | | 2020-04-30 | 220 | | 2020-04-30 | 221 | | 2020-05-01 | 222 | | 2020-05-01 | 222 | | 2020-05-01 | 223 | | 2020-05-04 | 224 | | 2020-05-04 | 225 | | 2020-05-04 | 226 | | 2020-05-04 | 226 | | 2020-05-04 | 227 | | 2020-05-04 | 228 | | 2020-05-05 | 229 | | 2020-05-05 | 230 | | 2020-05-05 | 231 | | 2020-05-05 | 232 | | 2020-05-06 | 233 | | 2020-05-06 | 234 | | ... | ... | +-------------+---------+
Bonus Read : How to Get Total Cumulative Users in MySQL
Here’s the SQL query to get daily new sign ups and users in last 12 months in MySQL.
mysql> select * from users
where date_joined> now() - INTERVAL 12 month;
In the above query, we use system function now() to get current datetime. Then we use INTERVAL clause to filter those records where order_date falls after an interval of 12 months before present datetime.
That’s it! As you can see it is quite easy to get last 12 months data in MySQL using INTERVAL clause.
If you want to create business dashboards, reports & charts from MySQL data, you can try Ubiq. We offer a 14-day free trial!
Sreeram Sreenivasan is the Founder of Ubiq. He has helped many Fortune 500 companies in the areas of BI & software development.