x

Aggregate Function Incorrect In Three Table Query

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

more ▼

asked Aug 29, 2010 at 05:02 PM in Default

Will gravatar image

Will
21 2 2 3

(comments are locked)
10|1200 characters needed characters left

2 answers: sort voted first

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
more ▼

answered Aug 29, 2010 at 08:03 PM

Oleg gravatar image

Oleg
15.9k 2 4 24

(comments are locked)
10|1200 characters needed characters left
Oleg thanks a million for this....worked a treat.
more ▼

answered Aug 30, 2010 at 05:06 AM

Will gravatar image

Will
21 2 2 3

(comments are locked)
10|1200 characters needed characters left
Your answer
toggle preview:

Up to 2 attachments (including images) can be used with a maximum of 524.3 kB each and 1.0 MB total.

New code box

There's a new way to format code on the site - the red speech bubble logo will automatically format T-SQL for you. The original code box is still there for XML, etc. More details here.

Follow this question

By Email:

Once you sign in you will be able to subscribe for any updates here

By RSS:

Answers

Answers and Comments

SQL Server Central

Need long-form SQL discussion? SQLserverCentral.com is the place.

Topics:

x116
x58
x31

asked: Aug 29, 2010 at 05:02 PM

Seen: 1503 times

Last Updated: Aug 30, 2010 at 06:03 AM