Data Structure
Networking
RDBMS
Operating System
Java
MS Excel
iOS
HTML
CSS
Android
Python
C Programming
C++
C#
MongoDB
MySQL
Javascript
PHP
Physics
Chemistry
Biology
Mathematics
English
Economics
Psychology
Social Studies
Fashion Studies
Legal Studies
- Selected Reading
- UPSC IAS Exams Notes
- Developer's Best Practices
- Questions and Answers
- Effective Resume Writing
- HR Interview Questions
- Computer Glossary
- Who is Who
How can I count the number of days in MySQL?
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)
Updated on: 30-Jul-2019
322 Views
Advertisements