Get 3 highest values from table

Question

I'd like to count the first 3 users who has the most attributed lines.

SQL Table:

ID | IdUser |  Type   |
-----------------------
0  |    1   |  like   |
1  |    1   |  like   |
2  |    4   | dislike |
3  |    5   | dislike |
4  |    1   |  like   |
5  |    4   |  like   |
6  |    5   |  like   |
8  |    4   |  like   |
9  |    4   |  like   |
10 |    3   |  like   |
11 |    5   |  like   |
12 |    9   |  like   |

Result should be:

idUser[1] with 3 times "like" and 0 "dislike" (3-0 = 3 points)
idUser[4] with 3 times "like" and 1 "dislike" (3-1 = 2 points)
idUser[5] with 2 times "like" and 1 "dislikes (2-1 = 1 point )

So what I'm trying to do is getting idUser 1 (3 points), then idUser4 (2 points) and finally idUser 5 (1 point) with their points.

I've tried different ways but none have worked.

Here I've tried to create a two-dimensional array with all data and then get the highest values but I couldn't do the second part.

Table 'users' has all users of the website table 'points' has likes and dislikes recorded

$sqlUsers = "SELECT * FROM users";
$resultUsers = $conn->query($sqlUsers);

$recordsArray = array(); //create array

while($rowUsers = $resultUsers->fetch_assoc()) {

    $idUser = $rowUsers['id'];

    //COUNT LIKES OF THE USER
    $sqlLikes = "SELECT COUNT(id) AS numberLikes FROM points WHERE idCibledUser='$idUser' AND type='like'";
    $resultLikes = $conn->query($sqlLikes);
    $rowLikes = $resultLikes->fetch_assoc();

    //COUNT DISLIKES OF THE USER
    $sqlDislikes = "SELECT COUNT(id) AS numberDislikes FROM points WHERE idCibledUser='$idUser' AND type='dislike'";
    $resultDislikes = $conn->query($sqlDislikes);
    $rowDislikes = $resultDislikes->fetch_assoc();

    //GET POINTS BY SUBTRACTING DISLIKES FROM LIKES
    $points = $rowLikes['numberLikes'] - $rowDislikes['numberDislikes'];

    $recordsArray[] = array($idUser => $points);

}

Answer 1

If you ultimately just need the total points without a breakdown of likes and dislikes (not totally clear from your question):

SELECT IdUser, SUM(IF(Type='like',1,-1)) AS points
FROM users
GROUP BY IdUser
ORDER BY points DESC
LIMIT 3

If you want the complete breakdown:

SELECT IdUser,
       SUM(IF(Type='like',1,-1)) AS points,
       SUM(IF(Type='like',1,0)) as likes,
       SUM(IF(Type='dislike',1,0)) as dislikes
FROM users
GROUP BY IdUser
ORDER BY points DESC
LIMIT 3

Explanation

Let's say I wanted to count the total number of rows where the Type column had the value 'like'. I could execute the following:

SELECT COUNT(*) AS cnt FROM users WHERE Type = 'like'

But another, perhaps less direct way, is the following:

SELECT SUM(IF(Type = 'like', 1, 0)) AS cnt FROM users

In the above SQL the Type column in each row is being examined and if equal to 'like', then the value of 1 is assigned to the column otherwise 0. Then all these 1's and 0's are added up using the SUM function. By adding up all the 1's, you are in effect counting the number of rows that had 'like' in the Type column. The second method allows you process the number of likes and dislikes with one pass:

SELECT SUM(IF(Type = 'like', 1, 0)) AS likes,
       SUM(IF(Type = 'dislike', 1, 0)) AS dislikes
       FROM users

But what if you wanted to get the above counts on a user by user basis? That is the purpose of the GROUP BY clause:

SELECT IdUser,
       SUM(IF(Type = 'like', 1, 0)) AS likes,
       SUM(IF(Type = 'dislike', 1, 0)) AS dislikes
       FROM users
       GROUP BY IdUser

The "score" or difference between the likes and dislikes can be computed if we assign the value of 1 to a column if it contains 'like' and a value of -1 if it contains 'dislike' (or isn't 'like') and then sum these values up:

SELECT IdUser,
       SUM(IF(Type = 'like', 1, -1)) AS points,
       SUM(IF(Type = 'like', 1, 0)) as likes,
       SUM(IF(Type = 'dislike', 1, 0)) as dislikes
FROM users
GROUP BY IdUser

Finally, if you want the three top scores, sort the returned rows in descending order (ORDER BY points DESC) and keep only the first 3 rows returned (LIMIT 3):

SELECT IdUser,
       SUM(IF(Type = 'like', 1, -1)) AS points,
       SUM(IF(Type = 'like', 1, 0)) as likes,
       SUM(IF(Type = 'dislike', 1, 0)) as dislikes
FROM users
GROUP BY IdUser
ORDER BY points DESC
LIMIT 3

Answer 2

See the solution below if you need to get likes/dislikes often, points table is being updated often and data relevance is important i.e. you don't want to cache the results.

Create another table like user_points_summary which will have 2 columns e.g. IdUser and Points. IdUser to be unique in this table, the Points recalculation (per user) must be triggered on adding new rows into the points table.

If you need likes/dislikes breakdown then this table will have 3 columns - IdUser (not unique anymore), likes_count, dislikes_count. And then the same - trigger this table update on inserting/updating/deleting rows in the points table.

If you go with the second option (with likes/dislikes breakdown) - here's an example of a create table statement:

CREATE TABLE `user_points_summary` (
  `IdUser` int(11) NOT NULL,
  `likes_count` int(11) NOT NULL DEFAULT '0',
  `dislikes_count` int(11) NOT NULL DEFAULT '0',
  KEY `idx_user_points_summary_IdUser` (`IdUser`)
) ENGINE=InnoDB;

Then you can add the following trigger to your users table which will add zero likes/dislikes on adding new users:

CREATE TRIGGER `users_AFTER_INSERT` AFTER INSERT ON `users` FOR EACH ROW
BEGIN
    INSERT INTO `user_points_summary` VALUE (NEW.`IdUser`, 0, 0);
END

Then add the following triggers to the points table to update user_points_summary likes/dislikes count:

DELIMITER $$
CREATE TRIGGER `points_AFTER_INSERT` AFTER INSERT ON `points` FOR EACH ROW
BEGIN
    IF NEW.`Type` = 'like' THEN
        UPDATE `user_points_summary` SET `likes_count` = `likes_count` + 1 WHERE `IdUser` = NEW.`IdUser`;
    ELSEIF NEW.`Type` = 'dislike' THEN
        UPDATE `user_points_summary` SET `dislikes_count` = `dislikes_count` + 1 WHERE `IdUser` = NEW.`IdUser`;
    END IF;
END $$

CREATE TRIGGER `points_AFTER_UPDATE` AFTER UPDATE ON `points` FOR EACH ROW
BEGIN
    IF NEW.`Type` = 'dislike' AND OLD.`Type` = 'like' THEN
        UPDATE `user_points_summary`
            SET 
                `likes_count` = `likes_count` - 1,
                `dislikes_count` = `dislikes_count` + 1
        WHERE `IdUser` = `OLD`.`IdUser`;
    ELSEIF NEW.`Type` = 'like' AND OLD.`Type` = 'dislike' THEN
        UPDATE `user_points_summary`
            SET 
                `dislikes_count` = `dislikes_count` - 1,
                `likes_count` = `likes_count` + 1
        WHERE `IdUser` = OLD.`IdUser`;
    END IF;
END $$

CREATE TRIGGER `points_AFTER_DELETE` AFTER DELETE ON `points` FOR EACH ROW
BEGIN
    IF OLD.`Type` = 'like' THEN
        UPDATE `user_points_summary`
            SET `likes_count` = `likes_count` - 1
        WHERE `IdUser` = `OLD`.`IdUser`;
    ELSEIF OLD.`Type` = 'dislike' THEN
        UPDATE `user_points_summary`
            SET `dislikes_count` = `dislikes_count` - 1
        WHERE `IdUser` = OLD.`IdUser`;
    END IF;
END $$
DELIMITER ;

Then you can use the following query to get user points with likes and dislikes count:

SELECT *, `likes_count` - `dislikes_count` AS `points` 
FROM `user_points_summary`
ORDER BY `points` DESC
LIMIT 3