Select total from a MySQL table based on month

For this, you can use GROUP BY MONTH(). Let us first create a table −

mysql> create table DemoTable1628     -> (     -> PurchaseDate date,     -> Amount int     -> ); Query OK, 0 rows affected (1.55 sec)

Insert some records in the table using insert command.

mysql> insert into DemoTable1628 values('2019-01-10',1500); Query OK, 1 row affected (0.68 sec) mysql> insert into DemoTable1628 values('2019-10-10',2000); Query OK, 1 row affected (0.61 sec) mysql> insert into DemoTable1628 values('2019-10-24',100); Query OK, 1 row affected (0.20 sec) mysql> insert into DemoTable1628 values('2019-11-10',500); Query OK, 1 row affected (0.17 sec) mysql> insert into DemoTable1628 values('2019-12-10',1600); Query OK, 1 row affected (0.32 sec) mysql> insert into DemoTable1628 values('2019-10-10',900); Query OK, 1 row affected (0.47 sec)

Display all records from the table using select statement −

mysql> select * from DemoTable1628;

This will produce the following output −

+--------------+--------+ | PurchaseDate | Amount | +--------------+--------+ | 2019-01-10   |   1500 | | 2019-10-10   |   2000 | | 2019-10-24   |    100 | | 2019-11-10   |    500 | | 2019-12-10   |   1600 | | 2019-10-10   |    900 | +--------------+--------+ 6 rows in set (0.00 sec)

Here is the query to get totals from database table based on month −

mysql> select month(PurchaseDate) as Month,year(PurchaseDate) as Year,SUM(Amount)     -> from DemoTable1628     -> group by month(PurchaseDate);

This will produce the following output −

+-------+------+-------------+ | Month | Year | SUM(Amount) | +-------+------+-------------+ |     1 | 2019 |        1500 | |    10 | 2019 |        3000 | |    11 | 2019 |         500 | |    12 | 2019 |        1600 | +-------+------+-------------+ 4 rows in set (0.20 sec)