Get the SUM of records between two given dates in MySQL

MySQLMySQLi Database

For this, use BETWEEN keyword. Let us first create a −

mysql> create table DemoTable1444
   -> (
   -> Value int,
   -> PurchaseDate datetime
   -> );
Query OK, 0 rows affected (0.45 sec)

Insert some records in the table using insert −

mysql> insert into DemoTable1444 values(40,'2019-01-10');
Query OK, 1 row affected (0.09 sec)
mysql> insert into DemoTable1444 values(100,'2019-10-03');
Query OK, 1 row affected (0.12 sec)
mysql> insert into DemoTable1444 values(170,'2019-11-21');
Query OK, 1 row affected (0.20 sec)
mysql> insert into DemoTable1444 values(70,'2018-12-05');
Query OK, 1 row affected (0.11 sec)

Display all records from the table using select −

mysql> select * from DemoTable1444;

This will produce the following output −

+-------+---------------------+
| Value | PurchaseDate        |
+-------+---------------------+
|    40 | 2019-01-10 00:00:00 |
|   100 | 2019-10-03 00:00:00 |
|   170 | 2019-11-21 00:00:00 |
|    70 | 2018-12-05 00:00:00 |
+-------+---------------------+
4 rows in set (0.00 sec)

Following is the query to get the sum between two given dates −

mysql> select sum(Value) from DemoTable1444 where PurchaseDate between '2019-10-02' and '2019-12-31';

This will produce the following output −

+------------+
| sum(Value) |
+------------+
|        270 |
+------------+
1 row in set (0.02 sec)
raja
Published on 12-Nov-2019 11:30:55
Advertisements