Self Join with Counts??? Need help pLEASE.

[trygvea]

I have one table that I need two counts from. The table holds information about a sports game. I want the number of games won and the number of games tied for a given team.

Table: Game
Fields: Game_ID,Home_Team_ID,Away_Team_ID,Win_Team_ID

If the game was a tie then Win_Team_ID = -1 instead of the approriate Team_ID.

Can someone please tell me what is wrong with my SQL;

Select Count(g2.Game_ID) As Tie_Count, Count(g1.Game_ID)
As Win_Count
From Game g2, Game g1
where (g1.Win_Team_ID = 8) or
((g2.Win_Team_ID =-1) and
(g2.Home_Team_ID = 8 or g2.Away_Team_ID = 8))

In this example I hard coded the Team_ID value, if I separate the query they each work fine, but when together I get too many rows returned and I get the same value for each count.

 

[tlbroadbent]

The query is slightly more complex than a self-join. Try this query. Copy and paste it into the SQL view of the query designer.

Select Team_ID, Sum(W) As WinCnt, Sum(T) AS TieCnt
From (
SELECT Win_Team_ID As Team_ID, Count(Win_Team_ID) AS W, 0 As T
FROM Game
Where Win_Team_ID<>-1
Group By Win_Team_ID
Union
SELECT Home_Team_ID As Team_ID, 0, Count(Home_Team_ID)
FROM Game
WHERE Win_Team_ID=-1
Group By Home_Team_ID
Union
SELECT Away_Team_ID As Team_ID, 0, Count(Away_Team_ID)
FROM Game
WHERE Win_Team_ID=-1
Group By Away_Team_ID
)
Group By Team_ID Terry

http://www.defendscouting.com/

&quot;I'm not dumb. I just have a command of thoroughly useless information.&quot; - Calvin, of Calvin and Hobbes