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:
SELECT
DATE(`datetime_column1`) AS 'alias1',
COUNT(*) AS 'alias2'
FROM `table_name`
GROUP BY DATE(`datetime_column1`);
Practical example
To show how to count rows per day, we will use the following table:
![MySQL - example data used to count rows per day](/static/bucket/1619163148157-k7AN9JoD90--image.png)
Note:
At the end of this article you can find database preparation SQL queries.
Example
In this example, we will display the date and the number of registered users for each day.
Query:
SELECT
DATE(`registration_time`) AS 'day',
COUNT(*) AS 'number_of_users'
FROM `users`
GROUP BY DATE(`registration_time`);
Result:
![MySQL - count rows per day - result](/static/bucket/1619163637193-WXQzap9Aw4--image.png)
Database preparation
create_tables.sql
file:
CREATE TABLE `users` (
`id` INT(10) UNSIGNED NOT NULL AUTO_INCREMENT,
`username` VARCHAR(50) NOT NULL,
`registration_time` DATETIME NOT NULL,
PRIMARY KEY (`id`)
);
insert_data.sql
file:
INSERT INTO `users`
(`username`, `registration_time`)
VALUES
('Tom', '2021-01-01 11:41:31'),
('Chris','2021-01-02 11:42:45'),
('Jack','2021-01-03 15:13:39'),
('Kim','2021-01-03 15:24:51'),
('Marco','2021-01-04 22:35:38'),
('Kate','2021-01-04 22:46:51'),
('Nam','2021-01-04 22:57:37');