Sum columns corresponding values according to similar dates in MySQL?
For this, use aggregate function SUM() along with GROUP BY. Let us first create a table −
mysql> create table DemoTable1522
-> (
-> ProductPurchaseDate date,
-> NumberOfProduct int
-> );
Query OK, 0 rows affected (1.51 sec)
Insert some records in the table using insert command −
mysql> insert into DemoTable1522 values('2019-01-21',45);
Query OK, 1 row affected (0.14 sec)
mysql> insert into DemoTable1522 values('2018-12-31',78);
Query OK, 1 row affected (0.12 sec)
mysql> insert into DemoTable1522 values('2019-01-21',67);
Query OK, 1 row affected (0.12 sec)
mysql> insert into DemoTable1522 values('2019-03-01',56);
Query OK, 1 row affected (0.19 sec)
mysql> insert into DemoTable1522 values('2018-01-21',97);
Query OK, 1 row affected (0.10 sec)
mysql> insert into DemoTable1522 values('2019-01-21',47);
Query OK, 1 row affected (0.15 sec)Display all records from the table using select statement −
mysql> select * from DemoTable1522;
This will produce the following output −
+---------------------+-----------------+
| ProductPurchaseDate | NumberOfProduct |
+---------------------+-----------------+
| 2019-01-21 | 45 |
| 2018-12-31 | 78 |
| 2019-01-21 | 67 |
| 2019-03-01 | 56 |
| 2018-01-21 | 97 |
| 2019-01-21 | 47 |
+---------------------+-----------------+
6 rows in set (0.00 sec)
Following is the query to count columns according to dates in MySQL −
mysql> select ProductPurchaseDate,sum(NumberOfProduct) from DemoTable1522
-> group by ProductPurchaseDate;
This will produce the following output −
+---------------------+----------------------+
| ProductPurchaseDate | sum(NumberOfProduct) |
+---------------------+----------------------+
| 2019-01-21 | 159 |
| 2018-12-31 | 78 |
| 2019-03-01 | 56 |
| 2018-01-21 | 97 |
+---------------------+----------------------+
4 rows in set (0.00 sec)
Published on 11-Dec-2019 10:59:13
