CODEWITHSUNDEEP
mysqlsql
Yusuf Devranlı
Yusuf Devranlı

Reputation: 159

Complicated Mysql Query? Summing Values when on same side in same match

  heroname  dmg    matchid  side
1 Fiora     700    match1   side1
2 Vayne     850    match1   side1
3 Katarina  300    match1   side1
4 Riven     200    match1   side2
5 Katarina  12390  match2   side1
6 Fiora     200    match3   side2
7 Vayne     500    match3   side2
8 Katarina  800    match3   side2

My query should sum DMGs when Fiora, Vayne and Katarina's when they are on the same side in a match.

If user types Fiora Vayne and Katarina in input,the site should show:

Heros                 DMG
Fiora Vayne Katarina 3350

3350 <= sum of row 1 2 3 6 7 8 dmgs.

2nd "Katarina"(5th row) didnt count because it was alone in another match. There were no Fiora and Vayne in its side.

If user types Riven Fiora Katarina in input form

No result should be showed because they are not on same side. Riven was on side 2 while fiora and katarina was on side 1.

How can i do this..

Its too complicated for me. My english is not enough to tell the problem properly with words. I hope you can understand what i want. Im stuck with this, i cant do it. I hope i dont get many downvotes.

Upvotes: 0

Views: 34

Answers (1)

Giorgos Betsos
Giorgos Betsos

Reputation: 72165

Try this:

SELECT 'Fiora, Vayne, Katarina' AS heroes, 
       SUM(sum_dmg)
FROM (
  SELECT SUM(dmg) AS sum_dmg
  FROM mytable
  WHERE heroname  IN ('Fiora', 'Vayne', 'Katarina')
  GROUP BY matchid, side
  HAVING COUNT(DISTINCT heroname) = 3) AS t

The subquery uses grouping in order to identify matches having all specified players on the same side. It returns the SUM of dmg values for all of these players. The outer query just calculates the total sum.

Edit:

For a more generic solution you can use GROUP_CONCAT as suggested by @Gordon:

SELECT heroes, 
       SUM(sum_dmg)
FROM (
  SELECT GROUP_CONCAT(DISTINCT heroname ORDER BY heroname) AS heroes,
         SUM(dmg) AS sum_dmg
  FROM mytable
  GROUP BY matchid, side
  HAVING COUNT(DISTINCT heroname) = 3) AS t
GROUP BY heroes

Demo here

Upvotes: 3

Related Questions