Select and filter the records on month basis in a MySQL table?
You can use aggregate function SUM() with GROUP BY clause to achieve this.
Let us create a table. The query to create a table is as follows −
mysql> create table SelectPerMonthDemo
-> (
-> Id int NOT NULL AUTO_INCREMENT PRIMARY KEY,
-> Price int,
-> PurchaseDate datetime
-> );
Query OK, 0 rows affected (2.34 sec)
Example
Insert some records in the table using insert command with one of them would be the date of purchase. The query is as follows −
mysql> insert into SelectPerMonthDemo(Price,PurchaseDate) values(600,date_add(now(),
interval -1 month));
Query OK, 1 row affected (0.42 sec)
mysql> insert into SelectPerMonthDemo(Price,PurchaseDate) values(600,date_add(now(),
interval 2 month));
Query OK, 1 row affected (0.34 sec)
mysql> insert into SelectPerMonthDemo(Price,PurchaseDate) values(400,now());
Query OK, 1 row affected (0.20 sec)
mysql> insert into SelectPerMonthDemo(Price,PurchaseDate) values(800,date_add(now(),
interval 3 month));
Query OK, 1 row affected (0.13 sec)
mysql> insert into SelectPerMonthDemo(Price,PurchaseDate) values(900,date_add(now(),
interval 4 month));
Query OK, 1 row affected (0.10 sec)
mysql> insert into SelectPerMonthDemo(Price,PurchaseDate) values(100,date_add(now(),
interval 4 month));
Query OK, 1 row affected (0.22 sec)
mysql> insert into SelectPerMonthDemo(Price,PurchaseDate) values(1200,date_add(now(),
interval -1 month));
Query OK, 1 row affected (0.09 sec)
Display all records from the table using a select statement. The query is as follows −
mysql> select *from SelectPerMonthDemo;
Output
The following is the output displaying the price and purchase date of the products −
+----+-------+---------------------+
| Id | Price | PurchaseDate |
+----+-------+---------------------+
| 1 | 600 | 2019-01-10 22:39:30 |
| 2 | 600 | 2019-04-10 22:39:47 |
| 3 | 400 | 2019-02-10 22:40:03 |
| 4 | 800 | 2019-05-10 22:40:18 |
| 5 | 900 | 2019-06-10 22:40:29 |
| 6 | 100 | 2019-06-10 22:40:41 |
| 7 | 1200 | 2019-01-10 22:40:50 |
+----+-------+---------------------+
7 rows in set (0.00 sec)
Here is the query to get the records according to individual months based on the Purchase Date −
mysql> select monthname(PurchaseDate) as MONTHNAME,sum(Price) from
SelectPerMonthDemo
-> group by monthname(PurchaseDate);
Output
+-----------+------------+
| MONTHNAME | sum(Price) |
+-----------+------------+
| January | 1800 |
| April | 600 |
| February | 400 |
| May | 800 |
| June | 1000 |
+-----------+------------+
5 rows in set (0.07 sec)
If you do not want the month name (only the month number), then use the following query −
mysql> select month(PurchaseDate),sum(Price) from SelectPerMonthDemo
-> group by month(PurchaseDate);
Output
+---------------------+------------+
| month(PurchaseDate) | sum(Price) |
+---------------------+------------+
| 1 | 1800 |
| 4 | 600 |
| 2 | 400 |
| 5 | 800 |
| 6 | 1000 |
+---------------------+------------+
5 rows in set (0.00 sec)
Published on 19-Mar-2019 06:54:17
