How To Get Last 12 Months Data in MySQL

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!

mm

About Ubiq

Ubiq is a dashboard & reporting platform for small & medium businesses. Ubiq makes it easy to build dashboards & reports for your business. Try it for free today!