JIYIK CN >

This article uses the GROUP BY clause, COUNT()and DATE()functions to group DATETIME type columns by DATE only in MySQL.

Use GROUP BY, COUNT(), and DATE() in MySQL to group a DATETIME column by DATE only

Before we proceed, remember that DATETIME values ​​look like YYYY-MM-DD hh:mm:ss and DATE values ​​are in the format YYYY-MM-DD. We only want to group the data by DATE when the data type of the column is DATETIME.

We will create two tables named students and student_attendance. The students table contains the basic details of each student, while the student_attendance table contains STUDENT_ID and ATTENDANCE as attributes (columns).

For learning purposes, we can also create these two tables using the queries below.

Sample code (create table):

# Create a `students` table
CREATE TABLE students(
   ID INT NOT NULL PRIMARY KEY,
    FIRSTNAME VARCHAR(45) NOT NULL,
    LASTNAME VARCHAR(45) NOT NULL,
    GENDER VARCHAR(10) NOT NULL
);

# Create a `student_attendance` table
CREATE TABLE student_attendance(
    STUDENT_ID INT NOT NULL,
    ATTENDANCE DATETIME NOT NULL,
    FOREIGN KEY (STUDENT_ID) REFERENCES students(ID)
);

Sample code (insert data into a table):

# Insert data into the `students` table
INSERT INTO students (ID, FIRSTNAME, LASTNAME, GENDER)
VALUES
(1, 'Mehvish', 'Ashiq', 'Female'),
(2, 'Thomas', 'Christopher', 'Male'),
(3, 'John', 'Jackson', 'Male');

# Insert data into the `stduent_attendance` table
INSERT INTO student_attendance (STUDENT_ID, ATTENDANCE)
VALUES
(1, '2022-05-02 08:15:10'),
(2, '2022-05-02 08:15:10'),
(3, '2022-05-02 08:15:10'),
(1, '2022-05-03 08:15:10'),
(2, '2022-05-03 08:15:10'),
(3, '2022-05-03 08:15:10'),
(1, '2022-05-04 08:15:10'),
(2, '2022-05-04 08:15:10'),
(3, '2022-05-04 08:15:10'),
(1, '2022-05-05 08:15:10'),
(2, '2022-05-05 08:15:10'),
(3, '2022-05-05 08:15:10'),
(1, '2022-05-06 08:15:10'),
(2, '2022-05-06 08:15:10'),
(3, '2022-05-06 08:15:10');

Sample code (display data):

SELECT * from students;
SELECT * from student_attendance;

Output (students table):

+----+-----------+-------------+--------+
| ID | FIRSTNAME | LASTNAME    | GENDER |
+----+-----------+-------------+--------+
|  1 | Mehvish   | Ashiq       | Female |
|  2 | Thomas    | Christopher | Male   |
|  3 | John      | Jackson     | Male   |
+----+-----------+-------------+--------+
3 rows in set (0.00 sec)

Output (for student_attendance table):

+------------+---------------------+
| STUDENT_ID | ATTENDANCE          |
+------------+---------------------+
|          1 | 2022-05-02 08:15:10 |
|          2 | 2022-05-02 08:15:10 |
|          3 | 2022-05-02 08:15:10 |
|          1 | 2022-05-03 08:15:10 |
|          2 | 2022-05-03 08:15:10 |
|          3 | 2022-05-03 08:15:10 |
|          1 | 2022-05-04 08:15:10 |
|          2 | 2022-05-04 08:15:10 |
|          3 | 2022-05-04 08:15:10 |
|          1 | 2022-05-05 08:15:10 |
|          2 | 2022-05-05 08:15:10 |
|          3 | 2022-05-05 08:15:10 |
|          1 | 2022-05-06 08:15:10 |
|          2 | 2022-05-06 08:15:10 |
|          3 | 2022-05-06 08:15:10 |
+------------+---------------------+
15 rows in set (0.04 sec)

Grouping a DATETIME column by DATE only in MySQL

We want to see how many students attend classes from Monday to Friday. We will group by DATE only as shown below.

Sample code:

SELECT COUNT(STUDENT_ID), DATE(ATTENDANCE)
FROM student_attendance
GROUP BY DATE(student_attendance.ATTENDANCE);

Output:

+-------------------+------------------+
| COUNT(STUDENT_ID) | DATE(ATTENDANCE) |
+-------------------+------------------+
|                 3 | 2022-05-02       |
|                 3 | 2022-05-03       |
|                 3 | 2022-05-04       |
|                 3 | 2022-05-05       |
|                 3 | 2022-05-06       |
+-------------------+------------------+
5 rows in set (0.00 sec)

Alternatively, we can use ALIAS to make the output clear.

Sample code:

SELECT COUNT(STUDENT_ID) AS NumbOfStudents, DATE(ATTENDANCE) AS DateOnly
FROM student_attendance
GROUP BY DATE(DateOnly);

Output:

+----------------+------------+
| NumbOfStudents | DateOnly   |
+----------------+------------+
|              3 | 2022-05-02 |
|              3 | 2022-05-03 |
|              3 | 2022-05-04 |
|              3 | 2022-05-05 |
|              3 | 2022-05-06 |
+----------------+------------+
5 rows in set (0.00 sec)

We converted the DATETIME type to DATE just to achieve the goal in the above query. We used different functions and clauses which are briefly explained below.

MySQL COUNT() Function

COUNT()is an aggregate function that we use to return the count of an expression. It lets us count all table records that satisfy a certain condition.

COUNT()The return type of the function is BIGINT. If there is no matching record in the table, it returns 0.

COUNT()We can use functions with SELECT statements in 3 ways .

1. COUNT(*) - This form generates output that includes all duplicate values, NULL, and NOT NULL values.
2. COUNT(expression) - This COUNT() function returns the number of records that do not have NULL values.
3. Count(distinct) − It returns the number of distinct records that do not have NULL value as the result of the expression.

MySQL DATE() function

DATE()The method extracts only the DATE from a DATETIME expression. If the expression is not a valid DATETIME or DATE value, it returns NULL.

MySQL GROUP BY clause

GROUP BYThe clause can group records with the same value into summary records. For example, find the number of students in a class.

We use this statement with aggregate functions to group the output by single or multiple fields (columns).