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.
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
Collected from the Internet
Please contact [email protected] to delete if infringement.
Comments