In this article I will share how to Select Count MySQL and group by ID from 2 different tables. The flow that I want to create is, I have 2 tables which I name posts and posts_likes. The posts table contains a list of users and posts_likes contains the number of likes for that user, see the picture below.
As a result, each user will have the number of likes based on user_id.
Make Table Command
The command to create the table is:
CREATE TABLE `posts` ( `id` INT NOT NULL AUTO_INCREMENT, `text` TEXT, `user_id` INT unsigned NOT NULL, `updated_at` TIMESTAMP NOT NULL, `created_at` TIMESTAMP NOT NULL, PRIMARY KEY (`id`) ); CREATE TABLE `post_likes` ( `post_id` INT unsigned NOT NULL, `user_id` INT unsigned NOT NULL, `created_at` TIMESTAMP NOT NULL );
Once created, let’s input some arbitrary data. after the data is inputted, then let’s read it and show the number of likes
Query Data By ID and Count Row
How to count value with same user_id? please use this command:
SELECT posts.*, COUNT(post_likes.user_id) as Likes from posts LEFT JOIN post_likes ON posts.user_id = post_likes.user_id GROUP BY posts.id;
I hope this short article about Select Count mysql and group by ID from 2 table can help you.