MySQL query problems with combined SUM

Question

I have three tables here, that I'm trying to do a tricky combined query on.

Table 1(teams) has Teams in it:

id     name
------------
150    LA Lakers
151    Boston Celtics
152    NY Knicks

Table 2(scores) has scores in it:

id  teamid   week    score
---------------------------
1     150     5        75
2     151     5        95
3     152     5        112

Table 3(tickets) has tickets in it

id    teamids    week
---------------------
1   150,152,154   5   
2   151,154,155   5

I have two queries that I'm trying to write Rather than trying to sum these each time i query the tickets, I've added a weekly_score field to the ticket. The idea being, any time a new score is entered for the team, I could take that teams id, get all tickets that have that team / week combo, and update them all based on the sum of their team scores.

I've tried the following to get the results i'm looking for (before I try and update them):

SELECT t.id, t.teamids, (
  SELECT SUM( s1.score ) 
  FROM scores s1
  WHERE s1.teamid
   IN (
    t.teamids
   )
 AND s1.week =11
) AS score
FROM tickets t
WHERE t.week =11
AND (t.teamids LIKE  "150,%" OR t.teamids LIKE  "%,150")

Not only is the query slow, but it also seems to not return the sum of the scores, it just returns the first score in the list.

Any help is greatly appreciated.

You cannot substitute a comma-separated column value `(t.teamids)` for an `IN()` clause. MySQL is seeing that as a single string in the `IN()`clause as in `IN ('150,152,154')` instead of 3 distinct values. — Michael Berkowski, Nov 30 '12 at 16:05
The best solution here is to properly normalize the `tickets` table to include one row per `teamid` — Michael Berkowski, Nov 30 '12 at 16:05
You should normalize your tickets table, with one record per team and week. — WoMo, Nov 30 '12 at 16:07
@MichaelBerkowski Well that explains why i'm only getting one row back for scores.... I realize I should normalize the tickets table, but I didn't write it and don't think modifying the database structure is in the scope of this project. — user1866795, Nov 30 '12 at 16:39

kfa · Answer 1 · 2012-11-30T16:18:55.407

0

You don't need SUM function here ? The scores table already has it? And BTW, avoid subqueries, try the left join (or left outer join depending on your needs).

SELECT t.id, t.name, t1.score, t2.teamids 
FROM teams t 
LEFT JOIN scores t1 ON t.id = t1.teamid AND t1.week = 11
LEFT JOIN tickets t2 ON t2.week = 11 
WHERE t2.week = 11 AND t2.teamids LIKE "%150%"

Not tested.

edited Nov 30 '12 at 16:18

answered Nov 30 '12 at 16:12

kfa

2,106
4
17
22

The scores table doesn't have a SUM in it. It has one score per team, whereas each ticket could have 4-5 teams assigned to it. I'm not that advanced at MySQL, I've never actually used JOINs before... – user1866795 Nov 30 '12 at 16:35

score 0 · Accepted Answer · answered Nov 30 '12 at 16:15

If you are going to match, you'll need to accommodate for the column only having one team id. Also, you'll need to LIKE in your SELECT sub query.

SELECT t.id, t.teamids, (
  SELECT SUM( s1.score ) 
  FROM scores s1
  WHERE 
    (s1.teamid LIKE t.teamids 
        OR CONCAT("%,",s1.teamid, "%") LIKE t.teamids 
        OR CONCAT("%",s1.teamid, ",%") LIKE t.teamids
    )
    AND s1.week =11
) AS score
FROM tickets t 
WHERE t.week =11
AND (t.teamids LIKE  "150,%" OR t.teamids LIKE  "%,150" OR t.teamids LIKE "150")

score 0 · Answer 3 · edited May 23 '17 at 12:00

Well not the most elegant query ever, but it should word:

SELECT
  tickets.id,
  tickets.teamids,
  sum(score)
FROM
  tickets left join scores
  on concat(',', tickets.teamids, ',') like concat('%,', scores.teamid, ',%')
WHERE tickets.week = 11 and concat(',', tickets.teamids, ',') like '%,150,%'
GROUP BY tickets.id, tickets.teamids

or also this:

SELECT
  tickets.id,
  tickets.teamids,
  sum(score)
FROM
  tickets left join scores
  on FIND_IN_SET(scores.teamid, tickets.teamids)>0
WHERE tickets.week = 11 and FIND_IN_SET('150', tickets.teamids)>0
GROUP BY tickets.id, tickets.teamids

(see this question and the answers for more informations).

MySQL query problems with combined SUM

3 Answers3