Order query result based off of information in another table

Question

I have a table called workouts and another table called likedWorkouts. If the user likes or dislikes a workout then I insert the Users ID, the workoutID of the liked workout and true or false into the table called likedWorkouts. The database structure is the same as the accepted answer in this question SQL database structure for Like and DisLike

I am trying to retrieve the workouts from the workouts table ordered by the number of likes each one has but since there is no Likes column and the only way I can get the number of likes a workout has is by doing a COUNT(*) query in the likedworkouts table how am I supposed to order the workouts table by the number of likes they have?

EDIT

So I got it working the only problem is I have another table called Downloads with two columns userID and WorkoutID I need to get the amount of downloads a workout has so I added LEFT JOIN DownloadedWorkouts AS d ON w.ID = d.WorkoutID to your alternative rating system query but it causes the likes and dislikes value to be wrong. Have any idea why?

EDIT2:

So i can get the downloads with this query

SELECT *
FROM (SELECT w.ID,
IFNULL(SUM(CASE WHEN d.WorkoutID THEN 1 ELSE 0 END), 0) AS downloads
      FROM UserWorkouts AS w 
      LEFT JOIN Profiles ON w.CreatorID = Profiles.UserID LEFT JOIN DownloadedWorkouts AS d ON w.ID =      d.WorkoutID
     GROUP BY w.ID
   ) AS r;

How can I add this to your alternative query?

Jonathan Leffler · Accepted Answer

SELECT w.WorkoutID, COUNT(*) AS likeCount
  FROM Workouts AS w
  JOIN LikedWorkouts AS l
    ON w.WorkoutID = l.WorkoutID
 WHERE l.liked = TRUE  -- You might need an alternative spelling of TRUE
 GROUP BY w.WorkoutID
 ORDER BY likeCount DESC;  -- You might need ORDER BY COUNT(*) DESC

This doesn't account for the dislikes at all; the highest ranking result could be one with 1000 likes and 5000 dislikes (which would be rated ahead of one with 300 likes and 0 dislikes). If you want to report on likes and dislikes separately, or the net sum of likes minus dislikes, or percentage of likes vs dislikes, then you have to modify the query, sometimes modestly radically.

This also doesn't show any workouts which are not liked at all (either because all the values in LikedWorkouts are FALSE or because no-one has registered an opinion on the workout). There are ways to deal with that, too, but the specification needs to be made clearer if that's what is required.

The more complete specification of the ranking function seems to be 'count of likes' divided by 'count of likes plus count of dislikes' where a workout with no likes or dislikes has a 0 likes and 0 dislikes (and a workout with no likes or dislikes will be treated the same as a workout which has 1000 dislikes and no likes).

For this, I tend to use TDQD — test-driven query design.

Calculate number of likes and dislikes for each workout that is liked or disliked

SELECT WorkoutID,
       SUM(CASE WHEN liked THEN 1 ELSE 0 END) AS likes,
       SUM(CASE WHEN liked THEN 0 ELSE 1 END) AS dislikes
  FROM LikedWorkouts
 GROUP BY WorkoutID

Generate 0 likes and dislikes for a workout with no entries in LikedWorkouts

This tends to be DBMS-specific, and SQL Server isn't my primary platform. It is probably easiest to use a function such as NVL or IFNULL (along with a LEFT JOIN). SQL Server seems to use IFNULL.

SELECT w.WorkoutID,
       IFNULL(SUM(CASE WHEN l.liked THEN 1 ELSE 0 END), 0) AS likes,
       IFNULL(SUM(CASE WHEN l.liked THEN 0 ELSE 1 END), 0) AS dislikes
  FROM Workouts AS w
  LEFT JOIN LikedWorkouts AS l ON w.WorkoutID = l.WorkoutID
 GROUP BY WorkoutID

Calculate fraction of likes

SELECT WorkoutID, likes / (likes + dislikes) AS rating
  FROM (SELECT w.WorkoutID,
               IFNULL(SUM(CASE WHEN l.liked THEN 1 ELSE 0 END), 0) AS likes,
               IFNULL(SUM(CASE WHEN l.liked THEN 0 ELSE 1 END), 0) AS dislikes
          FROM Workouts AS w
          LEFT JOIN LikedWorkouts AS l ON w.WorkoutID = l.WorkoutID
         GROUP BY WorkoutID
       ) AS r
 ORDER BY 2;  -- Or rating, or likes / (likes + dislikes)

There's a residual problem to be resolved — divide by zero when there are no ratings for a given workout.

Alternative ranking

In some respects, a better ranking function would subtract the count of dislikes from the count of likes and divide by the sum of the counts of the likes and dislikes. Then the unranked workout would be in the middle, ranked as 0%; the workout with only dislike votes would be -100%; and the workout with only like votes would be +100%. Only the last query needs changing, and the rating it generates is not a percentage but just a fraction -1.00 .. +1.00.

SELECT WorkoutID, (likes - dislikes) / (likes + dislikes) AS rating
  FROM (SELECT w.WorkoutID,
               IFNULL(SUM(CASE WHEN l.liked THEN 1 ELSE 0 END), 0) AS likes,
               IFNULL(SUM(CASE WHEN l.liked THEN 0 ELSE 1 END), 0) AS dislikes
          FROM Workouts AS w
          LEFT JOIN LikedWorkouts AS l ON w.WorkoutID = l.WorkoutID
         GROUP BY WorkoutID
       ) AS r
 ORDER BY 2;  -- Or rating, or (likes - dislikes) / (likes + dislikes)

This has the same divide by zero issue to be resolved.

Fix the divide by zero

I think (untested) you can fix the divide by zero problem with:

SELECT WorkoutID,
       CASE
       WHEN (likes + dislikes) != 0
       THEN (likes - dislikes) / (likes + dislikes)
       ELSE 0
       END AS rating
  FROM (SELECT w.WorkoutID,
               IFNULL(SUM(CASE WHEN l.liked THEN 1 ELSE 0 END), 0) AS likes,
               IFNULL(SUM(CASE WHEN l.liked THEN 0 ELSE 1 END), 0) AS dislikes
          FROM Workouts AS w
          LEFT JOIN LikedWorkouts AS l ON w.WorkoutID = l.WorkoutID
         GROUP BY WorkoutID
       ) AS r
 ORDER BY 2;  -- Or rating, or (likes - dislikes) / (likes + dislikes)

Warning: all SQL as yet untested on any DBMS, much less SQL Server.

(NB: I generally avoid ORDER BY 2 which was part of the 1986 SQL standard. Many DBMS allow you to sort by column aliases from the select-list; others may require you to repeat the expression from the select-list in the ORDER BY clause (which is a sad case of violating the DRY (Don't Repeat Yourself) Principle. Using ORDER BY 2 is likely to work in most DBMS and bypasses the problems — but isn't very desirable. I don't know SQL Server well enough to know where it falls on the spectrum w.r.t this issue.)

Adding downloads

The question now suggests this as a way of calculating the downloads:

SELECT *
FROM (SELECT w.ID,
             IFNULL(SUM(CASE WHEN d.WorkoutID THEN 1 ELSE 0 END), 0) AS downloads
        FROM UserWorkouts AS w 
        LEFT JOIN Profiles ON w.CreatorID = Profiles.UserID
        LEFT JOIN DownloadedWorkouts AS d ON w.ID = d.WorkoutID
       GROUP BY w.ID
   ) AS r;

This wraps a SELECT * FROM (...) AS r around the necessary query — for the downloads, you could simply use:

SELECT w.ID,
       IFNULL(SUM(CASE WHEN d.WorkoutID THEN 1 ELSE 0 END), 0) AS downloads
  FROM UserWorkouts AS w
  LEFT JOIN Profiles ON w.CreatorID = Profiles.UserID
  LEFT JOIN DownloadedWorkouts AS d ON w.ID = d.WorkoutID
 GROUP BY w.ID

However, this query has three new tables; none of the tables clearly corresponds to those in the original question. Maybe UserWorkouts is what was previously Workouts. I don't see why Profiles is involved; it looks like you should be just using the list of all workouts and the DownloadedWorkouts tables. (In future, please give at least skeletal schemas for the tables in the question; it would simplify things for everyone — for you translating answers, and for us giving you answers.)

SELECT w.WorkoutID,
       IFNULL(SUM(CASE WHEN d.WorkoutID THEN 1 ELSE 0 END), 0) AS downloads
  FROM Workouts AS w
  LEFT JOIN DownloadedWorkouts AS d ON w.WorkoutID = d.WorkoutID
 GROUP BY w.ID

Note that I've used the Workouts table and assumed that the primary key column is WorkoutID as before.

Combining that with the main query gives:

SELECT r.WorkoutID, r.Rating, d.Downloads
  FROM (SELECT WorkoutID,
               CASE
               WHEN (likes + dislikes) != 0
               THEN (likes - dislikes) / (likes + dislikes)
               ELSE 0
               END AS rating
          FROM (SELECT w.WorkoutID,
                       IFNULL(SUM(CASE WHEN l.liked THEN 1 ELSE 0 END), 0) AS likes,
                       IFNULL(SUM(CASE WHEN l.liked THEN 0 ELSE 1 END), 0) AS dislikes
                  FROM Workouts AS w
                  LEFT JOIN LikedWorkouts AS l ON w.WorkoutID = l.WorkoutID
                 GROUP BY WorkoutID
               ) AS r1
       ) AS r
  JOIN (SELECT w.ID AS WorkoutID,
               IFNULL(SUM(CASE WHEN d.WorkoutID THEN 1 ELSE 0 END), 0) AS downloads
          FROM Workouts AS w
          LEFT JOIN DownloadedWorkouts AS d ON w.WorkoutID = d.WorkoutID
         GROUP BY w.ID
       ) AS d
    ON r.WorkoutID = d.WorkoutID
 ORDER BY r.rating, d.WorkoutID;

This is a good demonstration of TDQD in action. You can create and test the various sub-queries as the development proceeds, ending up with a solid answer even when the resulting query is rather complex. I'd hate to try to write this final query from scratch; indeed, I wouldn't — I'd use TDQD to get the parts right, pretty much as shown.

NB: All SQL still untested by me.