MSSQL Help! Just can't get this to work!
I'm using MSSQL 2005 and the following in my existing query.
The problem is that when there is no team record in Bonus table I don't end up with any Total Average, even with data in the others. When there is a team record in the Bonus table, the TotalAverage works great.
Anybody spot the problem?
SELECT TOP (10) TT.TeamID, TT.TeamName, TT.Region, ROUND((DisT.TotalDistance + BT.TotalBonus) / PRTT.TotalParticipants, 1) AS TotalAverage
FROM tblTeams AS TT INNER JOIN
(SELECT TeamID, COUNT(*) AS TotalParticipants
FROM tblParticipants AS tblParticipants_1
GROUP BY TeamID) AS PRTT ON TT.TeamID = PRTT.TeamID INNER JOIN
(SELECT TeamID, ISNULL(SUM(Distance), 0) AS TotalDistance
GROUP BY TeamID) AS DisT ON TT.TeamID = DisT.TeamID INNER JOIN
(SELECT TeamID, ISNULL(SUM(Bonus), 0) AS TotalBonus
WHERE (Show = 1)
GROUP BY TeamID) AS BT ON TT.TeamID = BT.TeamID
ORDER BY TotalAverage DESC
the problem is that you're using all inner joins
Thanks! I think I have it now ...
Users Browsing this Thread
There are currently 1 users browsing this thread. (0 members and 1 guests)
Tags for this Thread