Agrupe la columna de fecha y hora solo por fecha en MySQL
Este tutorial utiliza la cláusula GROUP BY, las funciones COUNT() y DATE() para agrupar la columna de tipo DATETIME por DATE solo en MySQL.
Use GROUP BY, COUNT() y DATE() para agrupar la columna DATETIME por DATE Solo en MySQL
Antes de continuar, recuerda que los valores de DATETIME se ven como YYYY-MM-DD hh:mm:ss mientras que los valores de DATE tienen el formato YYYY-MM-DD. Queremos agrupar los datos por DATE solo mientras el tipo de datos de la columna sea DATETIME.
Crearemos dos tablas llamadas students y student_attendance. La tabla students tiene detalles básicos sobre cada alumno, mientras que la tabla student_attendance contiene STUDENT_ID y ATTENDANCE como atributos (columnas).
También podemos crear ambas tablas utilizando las consultas a continuación con fines de aprendizaje.
Código de ejemplo (Crear tablas):
# 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)
);
Código de ejemplo (Insertar datos en las tablas):
# 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');
Código de ejemplo (Mostrar datos):
SELECT * from students;
SELECT * from student_attendance;
Salida (para la tabla estudiantes):
+----+-----------+-------------+--------+
| ID | FIRSTNAME | LASTNAME | GENDER |
+----+-----------+-------------+--------+
| 1 | Mehvish | Ashiq | Female |
| 2 | Thomas | Christopher | Male |
| 3 | John | Jackson | Male |
+----+-----------+-------------+--------+
3 rows in set (0.00 sec)
Salida (para la tabla student_attendance):
+------------+---------------------+
| 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)
Agrupe la columna DATETIME por DATE Solo en MySQL
Queremos comprobar cuántos estudiantes asistieron a la clase de lunes a viernes. Agruparemos por la FECHA solo de la siguiente manera.
Código de ejemplo:
SELECT COUNT(STUDENT_ID), DATE(ATTENDANCE)
FROM student_attendance
GROUP BY DATE(student_attendance.ATTENDANCE);
Producción :
+-------------------+------------------+
| 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)
Alternativamente, podemos usar ALIAS para aclarar la salida.
Código de ejemplo:
SELECT COUNT(STUDENT_ID) AS NumbOfStudents, DATE(ATTENDANCE) AS DateOnly
FROM student_attendance
GROUP BY DATE(DateOnly);
Producción :
+----------------+------------+
| 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)
Convertimos el tipo DATETIME a DATE solo para lograr el objetivo en la consulta anterior. Utilizamos diferentes funciones y cláusulas que se explican brevemente a continuación.
Función CONTAR() de MySQL
El COUNT() es una función agregada que usamos para devolver el recuento de la expresión. Nos permite contar todos los registros de la tabla que cumplen una determinada condición.
El tipo de retorno de la función COUNT() es BIGINT. Devuelve 0 si no hay registros coincidentes en la tabla.
Hay 3 formas en que podemos usar la función COUNT() con la instrucción SELECT.
Recuento (*): la salida producida por este formulario contiene todos los valores duplicados,NULOSyNO NULOS.Cuenta (expresión)- El número de registros devueltos por esta funciónCUENTA()no tiene los valoresNULO.Count (distinct)- Devuelve el número de registros distintos que no tienen como resultado de una expresión los valoresNULL.
Función FECHA() de MySQL
El método DATE() extrae solo el DATE de la expresión DATETIME. Si la expresión no es un valor DATETIME o DATE válido, devuelve NULL.
Cláusula GROUP BY de MySQL
La cláusula GROUP BY puede agrupar los registros con los mismos valores en los registros de resumen. Por ejemplo, encuentre el número de estudiantes presentes en la clase.
Usamos esta declaración con las funciones agregadas para agrupar la salida por campos únicos o múltiples (columnas).
