CODEWITHSUNDEEP
mysql
AnkitJ
AnkitJ

Reputation: 356

Getting table rows in order of sum of count of two another columns from another table

I have 3 MYSQL tables they are:

POST

mysql> DESCRIBE `posts`;
+------------------+------------------+------+-----+---------+----------------+
| Field            | Type             | Null | Key | Default | Extra          |
+------------------+------------------+------+-----+---------+----------------+
| post_id          | int(11) unsigned | NO   | PRI | NULL    | auto_increment |
| post_details     | varchar(11)      | NO   |     | NULL    |                |                |
+------------------+------------------+------+-----+---------+----------------+

Likes

mysql> DESCRIBE `likes`;
+------------------+------------------+------+-----+---------+----------------+
| Field            | Type             | Null | Key | Default | Extra          |
+------------------+------------------+------+-----+---------+----------------+
| id               | int(11) unsigned | NO   | PRI | NULL    | auto_increment |
| post_id          | int(11)          | NO   |     | NULL    |                |
| user_id          | int(11)          | NO   |     | NULL    |                |                |
+------------------+------------------+------+-----+---------+----------------+

Comments

mysql> DESCRIBE `comments`;
+------------------+------------------+------+-----+---------+----------------+
| Field            | Type             | Null | Key | Default | Extra          |
+------------------+------------------+------+-----+---------+----------------+
| id               | int(11) unsigned | NO   | PRI | NULL    | auto_increment |
| post_id          | int(11)          | NO   |     | NULL    |                |
| user_id          | int(11)          | NO   |     | NULL    |                |                |
+------------------+------------------+------+-----+---------+----------------+

In 2nd and 3rd table, I am storing likes and comments of posts( 1st table ). Now, I want to get posts in descending order of sum of count of likes and comments. i.e. posts will be in order of maximum number of likes + comments to minimum number. Please help me to get right result and thanks in advance.

Upvotes: 2

Views: 50

Answers (2)

Gianmarco
Gianmarco

Reputation: 2552

SELECT posts.post_id, post_details,
    ((SELECT(COUNT(id) FROM comments WHERE comments.post_id = posts.post_id)) + (SELECT(COUNT(id) FROM likes WHERE likes.post_id = posts.post_id))) AS weight
FROM posts
ORDER BY weight DESC

Without using variables you can use nested selects

Upvotes: 1

Cyval
Cyval

Reputation: 2519

Try:

@likes = SELECT COUNT(id) FROM likes;
@comments = SELECT COUNT(id) FROM comments;
SELECT post_id, (@likes + @comments) AS TotalStats  FROM posts GROUP BY post_id ORDER BY TotalStats DESC;

Upvotes: 0

Related Questions