Take the 2-minute tour ×
Database Administrators Stack Exchange is a question and answer site for database professionals who wish to improve their database skills and learn from others in the community. It's 100% free, no registration required.

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"
share|improve this question
    
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

1 Answer 1

up vote 1 down vote accepted
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.)

Based on comments

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;
share|improve this answer
    
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

Your Answer

 
discard

By posting your answer, you agree to the privacy policy and terms of service.

Not the answer you're looking for? Browse other questions tagged or ask your own question.