
- Trending Categories
Data Structure
Networking
RDBMS
Operating System
Java
iOS
HTML
CSS
Android
Python
C Programming
C++
C#
MongoDB
MySQL
Javascript
PHP
- Selected Reading
- UPSC IAS Exams Notes
- Developer's Best Practices
- Questions and Answers
- Effective Resume Writing
- HR Interview Questions
- Computer Glossary
- Who is Who
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)
- Related Questions & Answers
- MySQL query to select records from a table on the basis of a particular month number?
- Select records from a table on the basis of keywords in MySQL
- Select total from a MySQL table based on month
- Filter the records of current day, month and year in MySQL?
- Fetching records from a table with NULL and other values on the basis of conditions in MySQL
- Search records on the basis of date in MySQL?
- Fetch records on the basis of LastName using MySQL IN()
- Filter dates from a table with DATE and NULL records in MySQL
- MySQL to fetch records based on a specific month and year?
- Do a select in MySQL based only on month and year?
- MySQL SELECT query to return records with specific month and year
- Display records on the basis of key-value pairs in MySQL
- How can fetch records from specific month and year in a MySQL table?
- Select items based on value first, then order on the basis of date for rest of the records in MySQL
- Select the topmost record from a table ordered by desc on the basis of ID?
Advertisements