Two queries work independently but can not conjoin them

Question

I have a two queries which work independently but when combined are throwing incorrect results;

Query 1:

SELECT 
    ID, 
    (FirstName || " " || Surname) AS Player, 
    COUNT (PlayerMatch.PlayerID) AS 'Matches', 
    SUM (PlayerMatch.MinuteOff - PlayerMatch.MinuteOn) AS 'Mins',
    SUM (PlayerMatch.Shots) AS 'Shots',
    SUM (PlayerMatch.ShotOnTarget) AS 'On Target',
    ROUND (1.0 * SUM (PlayerMatch.ShotOnTarget) / SUM (PlayerMatch.Shots), 2) AS 'Shots on target'
FROM 
    Players
LEFT JOIN 
    PlayerMatch ON PlayerMatch.PlayerID = Players.ID 
GROUP BY  
    Players.ID
HAVING 
    COUNT (PlayerMatch.PlayerID) > 0;

and query #2:

SELECT 
    ID, 
    (FirstName || " " || Surname) AS Player,
    COUNT (Goals.ScorerID) AS 'Goals',
FROM 
    Players
LEFT JOIN 
    Goals ON Goals.ScorerID = Players.ID 
GROUP BY  
    Players.ID
HAVING 
    COUNT (Goals.ScorerID) > 0
ORDER BY  
    COUNT (Goals.ScorerID) DESC;

When combined like this:

SELECT 
    ID, 
    (FirstName || " " || Surname) AS Player, 
    COUNT (PlayerMatch.PlayerID) AS 'Matches', 
    SUM (PlayerMatch.MinuteOff - PlayerMatch.MinuteOn) AS 'Mins',
    SUM (PlayerMatch.Shots) AS 'Shots',
    SUM (PlayerMatch.ShotOnTarget) AS 'On Target',
    COUNT (Goals.ScorerID) AS 'Goals',
    ROUND (1.0 * SUM (PlayerMatch.ShotOnTarget) / SUM (PlayerMatch.Shots), 2) AS 'Shots on target'
FROM 
    Players
LEFT JOIN 
    PlayerMatch ON PlayerMatch.PlayerID = Players.ID 
LEFT JOIN 
    Goals ON Goals.ScorerID = Players.ID 
GROUP BY  
    Players.ID
HAVING 
    COUNT (PlayerMatch.PlayerID) > 0 
    AND COUNT (Goals.ScorerID) > 0
ORDER BY  
    COUNT (Goals.ScorerID) DESC;

The column

    COUNT (Goals.ScorerID) AS 'Goals',

returns the correct number of results but the wrong details.

Any help greatly appreciated.

Answer 1

You have multiple matching records in both tables for a given player, so the joins multiply the rows, and the results of the aggregates are off. You would need to pre-aggregate in subqueries:

select p.id, p.firstname || ' ' || p.surname as player,
    g.goals,
    pm.matches, pm.mins, pm.shots, pm.on_target, pm.on_target_ratio
from players p
left join (select scorerid, count(*) as goals from goals group by scorerid) g
    on g.scorerid = p.id
left join (
    select playerid,
        count(*) as matches, 
        sum (minuteoff - minuteon) as mins
        sum(shots) as shots,
        sum(shotontarget) as on_target,
        round(1.0 * sum(shotontarget) / sum(shots), 2) as on_target_ratio
    from playermatch
    group by playerid
) pm on pm.Player_id = p.id

Depending on the database that you use, there may be better options, especially if it supports lateral joins.