Aggregate Function Incorrect In Three Table Query

Question

Hi all,

I would really appreciate your help with this one.

Table Name: Y2_Fixture (Relevant Fields: Fixture_ID)

Table Name: Y2_Player (Relevant Fields: Player_ID, Player_Type)

Table Name: Y2_PlayerHistory (Relevant Fields: playerhistory_FixtureID, playerhistory_Goals, playerhistory_PlayerID)

Data: Y2_Fixture

Fixture_ID

1

2

Data: Y2_PlayerHistory

playerhistory_FixtureID, playerhistory_Goals, playerhistory_PlayerID

1, 4, 222

2, 2, 223

1, 1, 224

1, 4, 225

2, 1, 225

Data: Y2_Player

player_ID, player_Type

222, C

223, C

224, C

225, O

226, O

I am trying to arrange, by fixture, SUM of goals. There needs to be two SUMs because goals need to be counted seperately for player types 'C' and 'O' in the Y2_Player table ('C' and 'O' determine whether the player plays for the club or is a member of the opposition).

Here's my SQL so far:

SELECT fixture_ID, SUM(CB.ClubGoals), SUM(OP.OppositionGoals)
FROM y2_fixture
LEFT OUTER JOIN
(SELECT playerhistory_FixtureID, playerhistory_Goals AS ClubGoals
FROM Y2_PlayerHistory INNER JOIN Y2_Player ON player_ID = playerhistory_PlayerID 
WHERE player_Type = 'O') AS CB ON Y2_Fixture.fixture_ID = CB.playerhistory_FixtureID LEFT OUTER JOIN 
(SELECT playerhistory_FixtureID, Playerhistory_Goals AS OppositionGoals
FROM dbo.Y2_PlayerHistory INNER JOIN Y2_Player ON player_ID = playerhistory_PlayerID 
WHERE player_Type = 'C') AS OP ON Y2_Fixture.fixture_ID = OP.playerhistory_FixtureID
GROUP BY Y2_Fixture.fixture_ID

The problem is that this gives me:

fixture_ID, ClubGoals, OppositionGoals

1, 5, 8

2, 2, 2

For some reason, some of the scores are doubled by the query. I have found that by messing with the query the effect varies, sometimes scores on one on only the Clubside are doubled, sometimes only the OppositionGoals figure. I've even had it double the score on the ClubGoals on one record and so the same to OppostionGoals figure on the next records, leaving Clubgoals with the correct score. I have tried splitting up the query and running sections of it individually but everything seems fine.

I know that I could achieve what I need by using multiple views but I am hoping to reduce the numbers of views used in the DB.

So to summarise, I am trying to join two different queries from the same table back to a master fixture table. Sorry if the above is long winded but I thought it best to give you all the info I could. I'm using MS SQL 2008.

Ladies and gents - any help you can offer would be greatly appreciated.

Will

Answer 1

0

This should do it:

select 
    F.Fixture_ID,
    sum(case 
            when p.player_Type = 'C' then PH.playerhistory_Goals 
            else 0 
        end) ClubGoals,
    sum(case 
            when p.player_Type = 'O' then PH.playerhistory_Goals 
            else 0 
        end) OppositionGoals
    from Y2_Fixture F inner join Y2_PlayerHistory PH
        on F.Fixture_ID = PH.playerhistory_FixtureID
    inner join Y2_Player P
       on PH.playerhistory_PlayerID = P.player_ID
    group by F.Fixture_ID;

Hope this helps,

Oleg

answered Aug 29 '10 at 08:03 PM

Oleg
15.4k ● 1 ● 4 ● 24

(comments are locked)

Answer 2

0

Oleg thanks a million for this....worked a treat.

answered Aug 30 '10 at 05:06 AM

Will
21 ● 2 ● 2 ● 3

(comments are locked)

Aggregate Function Incorrect In Three Table Query

Follow this question

Related Questions