MySQL divide by values from 2 subqueries

Question

The table structure is as follows:

Players (PlayerID, FirstName, LastName, PositionID)
Salaries (SalaryID, PlayerID, Amount)
ScoredGoals (GoalId, PlayerID, GameID, Minute)
Positions (PositionID, PositionName)

I need to find the salary paid per goal per position. This query is producing the correct results but I'm wondering if there is a more efficient way to do this. Any help is appreciated!

SELECT g.PositionName, 
(s.AvgSalPerPosition / g.GoalsPerPosition) as SalPerGoalPerPosition
FROM
    (#TOTAL GOALS PER POSITION
    SELECT po.PositionName, 
    po.PositionID,
    COUNT(GoalID) as GoalsPerPosition
    FROM ScoredGoals sg
    JOIN Players p ON sg.PlayerID = p.PlayerID 
    JOIN Positions po ON p.PositionID = po.PositionID
    GROUP BY po.PositionID) g
JOIN
    (#AVG SALARY PER POSITION
    SELECT po.PositionID, 
    AVG(Amount) as AvgSalPerPosition
    FROM Positions po
    JOIN Players p ON po.PositionID = p.PositionID
    JOIN Salaries s ON p.PlayerID = s.PlayerID
    GROUP BY po.PositionID) s ON g.PositionID = s.PositionID

Probably won't make a difference, but `COUNT(GoalID)` can be `COUNT(*)`. See https://stackoverflow.com/questions/2876909/count-and-countcolumn-name-whats-the-diff — Barmar, Oct 25 '17 at 22:03
@Barmar - that should be the same runplan. exactly how I would approach it as well b.digg, looks good...I guess we can point out that 1 row in scoredgoals = 1 goal is an assumption, but if that assumption is valid, you are good. Wheres the thumbs up emoji? — Twelfth, Oct 25 '17 at 22:30
Are you actually having performance problems with the query? Assuming you have indexes on all the columns you're joining on, I think it should perform well. — Barmar, Oct 25 '17 at 22:33
@Barmar No performance issues. Just wondering if two subqueries is the smart way to go about solving it. Thank you for the feedback! — b.digg, Oct 26 '17 at 00:26
You are relying on a functional dependence of positionid to positionName in the group by clause of the first subquery, and that might not work in other dbs (would need to group by both columns instead). Aside from that nitpicking, nice job. — Paul Maxwell, Oct 26 '17 at 02:42

score 0 · Answer 1 · answered Oct 26 '17 at 00:36

It's possible to do it with just one subquery instead of 2, by joining the second subquery with the tables in the first subquery.

SELECT po.positionName, s.avgSalPerPosition/COUNT(*) AS salPerGoalPerPosition
FROM ScoredGoals sg
JOIN Players p ON sg.PlayerID = p.PlayerID 
JOIN Positions po ON p.PositionID = po.PositionID
JOIN (
    SELECT po.PositionID, 
        AVG(Amount) as AvgSalPerPosition
    FROM Positions po
    JOIN Players p ON po.PositionID = p.PositionID
    JOIN Salaries s ON p.PlayerID = s.PlayerID
    GROUP BY po.PositionID) s 
ON po.PositionID = s.PositionID
GROUP BY po.PositionID

However, I suspect your version will perform better, because joining after aggregating means that the final join will be smaller -- it's just two derived tables whose sizes are the number of different positions.

MySQL divide by values from 2 subqueries

1 Answers1