ga_white
06-07-2009, 08:48 PM
Hi everyone,
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?
Many thanks,
G.
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
FROM tblRBCdata
GROUP BY TeamID) AS DisT ON TT.TeamID = DisT.TeamID INNER JOIN
(SELECT TeamID, ISNULL(SUM(Bonus), 0) AS TotalBonus
FROM tblRBC_Comments
WHERE (Show = 1)
GROUP BY TeamID) AS BT ON TT.TeamID = BT.TeamID
ORDER BY TotalAverage DESC
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?
Many thanks,
G.
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
FROM tblRBCdata
GROUP BY TeamID) AS DisT ON TT.TeamID = DisT.TeamID INNER JOIN
(SELECT TeamID, ISNULL(SUM(Bonus), 0) AS TotalBonus
FROM tblRBC_Comments
WHERE (Show = 1)
GROUP BY TeamID) AS BT ON TT.TeamID = BT.TeamID
ORDER BY TotalAverage DESC