How can I count the number of days in MySQL?

MySQLMySQLi Database

Let us first create a table with one column as datetime and another wherein the days are stored:

mysql> create table DemoTable
(
   ShippingDate datetime,
   CountOfDate int
);
Query OK, 0 rows affected (0.52 sec)

Following is the query to insert some records in the table using insert command:

mysql> insert into DemoTable values('2018-01-31',6);
Query OK, 1 row affected (0.11 sec)
mysql> insert into DemoTable values('2016-12-01',15);
Query OK, 1 row affected (0.21 sec)
mysql> insert into DemoTable values('2016-12-01',10);
Query OK, 1 row affected (0.13 sec)
mysql> insert into DemoTable values('2016-12-01',5);
Query OK, 1 row affected (0.17 sec)
mysql> insert into DemoTable values('2019-04-09',3);
Query OK, 1 row affected (1.05 sec)
mysql> insert into DemoTable values('2018-01-31',4);
Query OK, 1 row affected (0.14 sec)
mysql> insert into DemoTable values('2019-04-09',4);
Query OK, 1 row affected (0.14 sec)

Following is the query to display records from the table using select command:

mysql> select *from DemoTable;

This will produce the following output with Date and Count:

+---------------------+-------------+
| ShippingDate        | CountOfDate |
+---------------------+-------------+
| 2018-01-31 00:00:00 |           6 |
| 2016-12-01 00:00:00 |          15 |
| 2016-12-01 00:00:00 |          10 |
| 2016-12-01 00:00:00 |           5 |
| 2019-04-09 00:00:00 |           3 |
| 2018-01-31 00:00:00 |           4 |
| 2019-04-09 00:00:00 |          4 |
+---------------------+-------------+
7 rows in set (0.00 sec)

Following is the query to get the sum of each day:

mysql> select date(ShippingDate),sum(CountOfDate) from DemoTable group by date(ShippingDate);

This will produce the following output:

+--------------------+------------------+
| date(ShippingDate) | sum(CountOfDate) |
+--------------------+------------------+
| 2018-01-31         |               10 |
| 2016-12-01         |               30 |
| 2019-04-09         |                7 |
+--------------------+------------------+
3 rows in set (0.00 sec)
raja
Published on 29-Apr-2019 08:36:49
Advertisements