# Get maximum from aggregated values in a query

I have a table `details` it has a column `kills` and a column `player_slot` and a column `match_id`. I want to find the maximum difference between the `SUM` of `kills` for `player_slot` 0-4 and 128-132. Can someone give me a hint?

Thats my table `details`:

``````id,
kills,
match_id,
player_id,
player_slot
``````

Thats what I got so far, it's not much:

``````SELECT SUM(m."kills") AS max_kills
FROM details m
INNER JOIN matches ON matches.id = m.match_id
WHERE m.player_slot IN (0,1,2,3,4)
GROUP BY m."match_id"

SELECT SUM(m."kills") AS max_kills
FROM details m
INNER JOIN matches ON matches.id = m.match_id
WHERE m.player_slot IN (128,129,130,131,132)
GROUP BY m."match_id"
``````
-
You want the difference of the 2 maximums? – ypercubeᵀᴹ Oct 20 '13 at 20:07
Oh, I actually see thats wrong ;) no, I want the maximum difference between the 2 sums – vice_versa Oct 20 '13 at 20:14
So, this is something like 2 teams and you want to find the best performance of one team and the worst of the other (or the opposite.) But do you want to compare performances in the same "match" (and then find the max difference)? Or these performances (best and worst) are to be found in any "match", so possibly one match for team A and another for team B? – ypercubeᵀᴹ Oct 20 '13 at 20:17
yes this is 2 teams and each team player has a slot and has kills, so i want to sum the kills for each team and then find the best performance like 20:8 is better than 22:18 because the difference is bigger, I don't know if thats actually possible with SQL – vice_versa Oct 20 '13 at 20:22
So, if there are four matches, with 20:8, 22:18, 6:2 and 100:99, you want the 20:8 (and not the 100:2). Right? – ypercubeᵀᴹ Oct 20 '13 at 20:23

``````SELECT *
FROM  (
SELECT match_id
,SUM(CASE WHEN d.player_slot BETWEEN   0 AND   4
THEN d.kills ELSE 0 END) AS team1_kills
,SUM(CASE WHEN d.player_slot BETWEEN 128 AND 132
THEN d.kills ELSE 0 END) AS team2_kills
FROM   details d
WHERE (d.player_slot BETWEEN   0 AND   4
OR  d.player_slot BETWEEN 128 AND 132) -- parens optional
GROUP  BY d.match_id
) sub
ORDER  BY @(team1_kills - team2_kills) DESC
LIMIT  1;
``````
• The subquery is only for convenience.

• The `WHERE` can might improve performance if a matching index is available. Redundant otherwise.

• The `CASE` statements sum only kills from qualified players.

• The `@`- operator produces the absolute value of the score difference. This is used to sort by (descending), then we only need to pick the first.

• Don't use `m` as table alias for `details`, when a second table called `matches` is involved. That's confusing. Using `d` instead.

• More generally, why do you join to `matches` at all? It is not used in the query. Are there `details` without associated match to exclude? (Then it would make sense.)

The query could probably be simpler / faster this way:

``````SELECT *
FROM  (
SELECT match_id
,SUM(CASE WHEN p.team_id = ? THEN d.kills ELSE 0 END) AS team1_kills -- team1
,SUM(CASE WHEN p.team_id = ? THEN d.kills ELSE 0 END) AS team2_kills -- team2
FROM   details d
JOIN   players p ON p.id = d.player_slot  -- guessing from comment
WHERE  p.team_id  IN (?, ?)               -- teams to compare go here.
GROUP  BY d.match_id
) sub
ORDER  BY @(team1_kills - team2_kills) DESC
LIMIT  1;
``````
-
Sorry, you are right. I realized that and removed my comment. – ypercubeᵀᴹ Oct 20 '13 at 20:44
there is another table called players and this one has a team_id, btw. I have just tested your query, it is missing an alias for the subquery – vice_versa Oct 20 '13 at 20:45
@user30995: I added another version that might work better for your layout (guessing from your comment). Remember to post relevant table layout with such questions ... – Erwin Brandstetter Oct 20 '13 at 20:55
Umm, the `player_slot` is not the `id` from the players table, it is just a slot to differentiate the 2 teams – vice_versa Oct 20 '13 at 20:57
@user30995: So I didn't quite guess your design. If you post the (relevant) table layout, something more to the point might come off it. You probably get the idea, either way. – Erwin Brandstetter Oct 20 '13 at 21:05