CODEWITHSUNDEEP
mysqlsql
Aram Boyajyan
Aram Boyajyan

Reputation: 844

MySQL order results by total by weights

I have the following query:

SELECT
  p.`ID`,
  (SELECT COUNT(`ID`) FROM `comments` c WHERE c.`post_id` = p.`ID`) AS `comments`,
  (SELECT COUNT(`ID`) FROM `likes` l WHERE l.`post_id` = p.`ID`) AS `likes`
FROM `posts` p

I want to order the results according to the third column, which will be calculated the following way:

order = comments * 6 + likes * 4

How can I create this "virtual" column and use the results of the other two in a calculation?

Thanks!

Upvotes: 0

Views: 106

Answers (2)

fthiella
fthiella

Reputation: 49049

I would rewrite your query this way:

SELECT
    p.ID,
    COUNT(DISTINCT comments.ID) as comments,
    COUNT(DISTINCT likes.ID) as likes,
    COUNT(DISTINCT comments.ID)* 6 + COUNT(DISTINCT likes.ID)* 4 as `order`
FROM
    posts p LEFT JOIN comments on p.id = comments.post_id
    LEFT JOIN likes` ON likes.`post_id` = p.`ID`
GROUP BY
    p.ID
ORDER BY
    `order`

Upvotes: 1

Marc B
Marc B

Reputation: 360602

SELECT p.ID, comments, likes, (comments * 6) + (likes * 4) AS ordering
FROM (

    SELECT
      p.`ID`,
      (SELECT COUNT(`ID`) FROM `comments` c WHERE c.`post_id` = p.`ID`) AS `comments`,
      (SELECT COUNT(`ID`) FROM `likes` l WHERE l.`post_id` = p.`ID`) AS `likes`
    FROM `posts`
) AS p
ORDER BY ordering

Upvotes: 3

Related Questions