Sum values in MySQL from similar day records


Use GROUP BY and DATE() for this. Let us first create a table −

mysql> create table DemoTable1358
    -> (
    -> PurchaseDate datetime,
    -> ProductPrice int
    -> );
Query OK, 0 rows affected (1.59 sec)

Insert some records in the table using insert command. Here, we have inserted date records, with some records with similar dates −

mysql> insert into DemoTable1358 values('2019-09-20 12:34:00', 450);
Query OK, 1 row affected (0.23 sec)
mysql> insert into DemoTable1358 values('2019-09-21 11:00:00', 1050);
Query OK, 1 row affected (0.27 sec)
mysql> insert into DemoTable1358 values('2018-09-21 02:10:00', 2050);
Query OK, 1 row affected (0.18 sec)
mysql> insert into DemoTable1358 values('2019-09-21 05:20:40', 5050);
Query OK, 1 row affected (0.23 sec)
mysql> insert into DemoTable1358 values('2016-09-21 04:10:56', 1000);
Query OK, 1 row affected (0.18 sec)

Display all records from the table using select statement −

mysql> select * from DemoTable1358;

This will produce the following output −

+---------------------+--------------+
| PurchaseDate        | ProductPrice |
+---------------------+--------------+
| 2019-09-20 12:34:00 |          450 |
| 2019-09-21 11:00:00 |         1050 |
| 2018-09-21 02:10:00 |         2050 |
| 2019-09-21 05:20:40 |         5050 |
| 2016-09-21 04:10:56 |         1000 |
+---------------------+--------------+
5 rows in set (0.00 sec)

Following is the query to sum ProductPrice values from the same day −

mysql> select PurchaseDate,sum(ProductPrice) from DemoTable1358
    -> group by date(PurchaseDate);

This will produce the following output −

+---------------------+-------------------+
| PurchaseDate        | sum(ProductPrice) |
+---------------------+-------------------+
| 2019-09-20 12:34:00 |               450 |
| 2019-09-21 11:00:00 |              6100 |
| 2018-09-21 02:10:00 |              2050 |
| 2016-09-21 04:10:56 |              1000 |
+---------------------+-------------------+
4 rows in set (0.00 sec)
raja
Published on 05-Nov-2019 10:19:27
Advertisements