EN
MySQL - count rows per day
0 points
In this article, we would like to show you how to count rows per day in MySQL.
Quick solution:
xxxxxxxxxx1
SELECT2
DATE(`datetime_column1`) AS 'alias1', 3
COUNT(*) AS 'alias2'4
FROM `table_name`5
GROUP BY DATE(`datetime_column1`);To show how to count rows per day, we will use the following table:
Note:
At the end of this article you can find database preparation SQL queries.
In this example, we will display the date and the number of registered users for each day.
Query:
xxxxxxxxxx1
SELECT2
DATE(`registration_time`) AS 'day', 3
COUNT(*) AS 'number_of_users'4
FROM `users`5
GROUP BY DATE(`registration_time`);Result:
create_tables.sql file:
xxxxxxxxxx1
CREATE TABLE `users` (2
`id` INT(10) UNSIGNED NOT NULL AUTO_INCREMENT,3
`username` VARCHAR(50) NOT NULL,4
`registration_time` DATETIME NOT NULL,5
PRIMARY KEY (`id`)6
);insert_data.sql file:
xxxxxxxxxx1
INSERT INTO `users`2
(`username`, `registration_time`)3
VALUES4
('Tom', '2021-01-01 11:41:31'),5
('Chris','2021-01-02 11:42:45'),6
('Jack','2021-01-03 15:13:39'),7
('Kim','2021-01-03 15:24:51'),8
('Marco','2021-01-04 22:35:38'),9
('Kate','2021-01-04 22:46:51'),10
('Nam','2021-01-04 22:57:37');