CODEWITHSUNDEEP
mysqlsql
user3083672
user3083672

Reputation: 407

grouping by not working with multiple counts

I have the following SQL:

SELECT team_instance.id,COUNT(dropkicks.id) * 3
FROM team_instance
INNER JOIN player_instance ON team_instance.id = player_instance.team_instance_id
INNER JOIN dropkicks ON player_instance.id = dropkicks.kicker_id
WHERE dropkicks.success = true
GROUP BY team_instance.id

which works as expected - it returns the count value grouped by the team_instance id.

What I need to do is combine multiple counts so that the team_instance shows the total for two tables (penalties and dropkicks):

SELECT id AS team_instance_id,
(
SELECT COUNT(dropkicks.id) * 3
FROM team_instance
INNER JOIN player_instance ON team_instance.id = player_instance.team_instance_id
INNER JOIN dropkicks ON player_instance.id = dropkicks.kicker_id
WHERE dropkicks.success = true


) + 

(
SELECT COUNT(penalty_kicks.id) * 3
FROM team_instance
INNER JOIN player_instance ON team_instance.id = player_instance.team_instance_id
INNER JOIN penalty_kicks ON player_instance.id = penalty_kicks.kicker_id
WHERE penalty_kicks.success = true
)
AS total
 FROM team_instance
 GROUP BY team_instance.id

but this does't work properly as the values for both team_instances are the same (and they shouldn't be).

Upvotes: 1

Views: 36

Answers (2)

Gordon Linoff
Gordon Linoff

Reputation: 1269683

You need to correlate the subqueries to the outer query. Also, you do not need a group by assuming that id is, well, an id. The join to team_instance is also superfluous in the subqueries, so I think this is more what you want:

SELECT id AS team_instance_id,
       (SELECT COUNT(dropkicks.id) * 3
        FROM player_instance INNER JOIN
              dropkicks
              ON player_instance.id = dropkicks.kicker_id
        WHERE team_instance.id = player_instance.team_instance_id AND dropkicks.success = true
       ) + 
       (SELECT COUNT(penalty_kicks.id) * 3
        FROM player_instance INNER JOIN
             penalty_kicks
             ON player_instance.id = penalty_kicks.kicker_id
        WHERE team_instance.id = player_instance.team_instance_id AND penalty_kicks.success = true
       ) AS total
FROM team_instance;

The reason your values are the same is because your subqueries are aggregating over all the values in all the tables -- not counting things for a specific team.

Upvotes: 2

overflowed
overflowed

Reputation: 1838

youe need to put it in one select

SELECT team_instance.id, COUNT(dropkicks.id) * 3 + COUNT(penalty_kicks.id) * 3 as total
FROM team_instance
INNER JOIN player_instance ON team_instance.id = player_instance.team_instance_id
Left JOIN dropkicks ON player_instance.id = dropkicks.kicker_id and dropkicks.success = true
Left JOIN penalty_kicks ON player_instance.id = penalty_kicks.kicker_id and penalty_kicks.success = true
GROUP BY team_instance.id

Upvotes: 0

Related Questions