after left-join result, need a countresult per user

January 8, 2022

I have managed to create a query, which even works.

SELECT voterID,vote,nick as player
FROM 
trust LEFT JOIN players
ON trust.playerID=players.playerID
ORDER BY trust.vote DESC, trust.playerID DESC

This gives me a full list of all votes, but I want to SUM the votes per player, so i get 1 row per player, with 1 total amount of trustpoints.(can be positive or negative. +1 or -1 per voted, per voter)

The table trust:

CREATE TABLE `trust` (
  `rowID` int(10) UNSIGNED NOT NULL,
  `playerID` int(11) UNSIGNED NOT NULL,
  `voterID` int(11) UNSIGNED NOT NULL,
  `vote` int(11) NOT NULL
) ENGINE=InnoDB DEFAULT CHARSET=latin1;



INSERT INTO `trust` (`rowID`, `playerID`, `voterID`, `vote`) VALUES
(25, 2187, 1, 1),
(26, 23193, 1, 1),
(27, 2050, 1, 1),
(29, 3714, 1, 1),
(31, 1, 2187, 1),
(32, 30363, 29937, 1),
(33, 15837, 26102, 1),
(34, 30058, 26102, 1),
(35, 30539, 26102, -1),
(36, 28382, 26102, -1),
(37, 18692, 26102, 1),
(38, 6440, 14143, 1),
(39, 15069, 8306, 1),
(40, 2050, 2187, 1),
(41, 3233, 1, 1),
(42, 12664, 26102, 1),
(43, 30539, 2187, -1),
(44, 28382, 2187, -1),
(45, 30539, 1, -1),
(46, 10138, 1, 1);

Expecting result: a list of names and total SUM of votes

>Solution :

You can join the player table on an aggregate query of the votes:

SELECT   nick AS player, sum_votes
FROM     players p
JOIN     (SELECT   playerid, SUM(vote) AS sum_votes
          FROM     trust
          GROUP BY playerid) t ON p.playerid = t.playerid
ORDER BY 2 DESC