Select Count MySQL and GROUP BY ID From 2 Table

Posted on

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.

Select Count MySQL and group by ID from 2 table

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.

Read more:
> Install phpMyAdmin Ubuntu Linux Easy Terminal
> ERROR 1045 (28000) Access denied for user Ubuntu
> Install Laravel First Time In Ubuntu Linux

Leave a Reply

Your email address will not be published.