Select Count MySQL and GROUP BY ID From 2 Table

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.

Table of Contents

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.

Make Table Command

Query Data By ID and Count Row

Share this:

Related