# Listing teams by total distance and bonus

I had to write a SQL query recently to do a few joins and sums, and at the end of it I realized that I have nearly written a storybook. I know the tools to optimize it, but my trouble is the length of it. I am very sure, it can be shortened up, but my little brain refuses to strengthen this belief.

Basically, I want a list of teams ordered by the total Distance + Bonus. The Bonus consists of the team bonus and the bonus of team members (Member Bonus). In order to calculate distance covered by the team, I have to sum up the distance covered by the team members.

The relationship between the tables:

 SELECT TeamID, Name, Country, Region, OfficeLocation AS [Office Location], Minutes As Steps, Distance, SUM(MemberBonus+TeamBonus) AS Bonus,TeamSize,
ROW_NUMBER() OVER (ORDER BY SUM( ISNULL(Distance,0.0000000) +ISNULL(MemberBonus,0.0000000)+ISNULL(TeamBonus,0.0000000)) DESC) AS Place
FROM
(
-- Sum the Distance, Member Bonus and Team Bonus and assign them a rank based on the sum value.
SELECT TeamID, Name, Country, Region, Minutes, Distance,
ISNULL(MemberBonus,0.0000000) AS MemberBonus, SUM(ISNULL(teamBonusData.BonusPoints,0.0000000)) AS TeamBonus,
OfficeLocation, TeamSize
FROM
(
SELECT
Team.TeamID, Team.Name, Result.Country AS Country, Result.Region,
Result.Minutes AS Minutes,
SUM(ISNULL(Result.MemberBonus,0)) AS MemberBonus,
Result.Distance AS Distance,
TeamSize
FROM
(
SELECT Group1.TeamID, Group1.Name, Country, Region, Minutes, Distance, MemberBonus, TeamSize
FROM
(
--Get a sum of distance covered by the team's members. Only get the data for the active teams ( Status = 1)
SELECT
Team.TeamID, Team.Name, Country.Name AS Country, Region.Name AS Region,
ISNULL(SUM(Activity.Minutes),0) AS Minutes,
ISNULL(SUM(Activity.Distance),0) AS Distance
FROM
Team LEFT JOIN Country ON Team.fk_CountryID = Country.CountryID
LEFT JOIN Region ON Team.fk_RegionID = Region.RegionID
JOIN
TeamMember LEFT JOIN
Member LEFT JOIN Activity ON Activity.fk_MemberID = Member.MemberID
ON Member.MemberID = TeamMember.MemberID
ON Team.TeamID = TeamMember.TeamID
WHERE
Team.Status = 1 AND Member.Disabled = 0
GROUP BY
TeamMember.TeamID, Team.TeamID, Team.Name, Country.Name, Region.Name
)Group1
JOIN
(
-- Get a sum of Bonus points given to the team's members.
SELECT
TeamMember.TeamID, Member.MemberID, SUM(ISNULL(MemberBonus.BonusPoints,0)) AS MemberBonus
FROM
Team JOIN TeamMember
JOIN Member
LEFT JOIN dbo.MemberBonus
ON Member.MemberID = MemberBonus.fk_MemberID
ON TeamMember.MemberID = Member.MemberID
ON Team.TeamID = TeamMember.TeamID
GROUP BY
Member.MemberID, TeamMember.TeamID
) Group2
ON Group1.TeamID = Group2.TeamID
JOIN
(
-- Get the team size ( number of members in the team)
SELECT COUNT(TeamMember.TeamID) AS TeamSize,TeamID FROM TeamMember
GROUP BY TeamMember.TeamID
)Group3
ON Group1.TeamID = Group3.TeamID
)Result
JOIN Team ON Result.TeamID = Team.TeamID
GROUP BY
Team.TeamID, Team.Name, Result.Country, Result.Minutes, Result.Distance, Result.Region, Result.TeamSize
)teamRank
LEFT JOIN
(   --Get the Bonus points given to the team
SELECT ISNULL(TeamBonus.BonusPoints,0)AS BonusPoints, fk_TeamID  FROM TeamBonus
)teamBonusData
ON teamRank.TeamID = teamBonusData.fk_TeamID
LEFT JOIN
(
-- Get the office location value for the team's Captain
SELECT TeamID AS CapTeamID, OfficeLocation FROM TeamMember JOIN Member on TeamMember.MemberID = Member.MemberID WHERE MemberType='Captain'
)captainData
ON teamRank.TeamID = captainData.CapTeamID
GROUP BY
teamRank.TeamID, teamRank.Name, teamRank.Country, teamRank.Minutes, teamRank.Distance, teamRank.Region, teamRank.MemberBonus, captainData.OfficeLocation, teamRank.TeamSize
) myTeamRank
GROUP BY
myTeamRank.TeamID, myTeamRank.Name, myTeamRank.Country, myTeamRank.Minutes, myTeamRank.Distance, myTeamRank.Region, myTeamRank.OfficeLocation,TeamSize
ORDER BY
Place

• Hm ... you could create, fill and update a temporary table or two to simplify things. I doubt that you have millions of teams and team members. You could also use several CTEs ... blog.sqlauthority.com/2009/08/08/… – Leonid May 25 '12 at 0:39
• Thanks for the tip, Leonid! Temp tables sound a good idea. However, is there also any way to shorten this query? Just want to polish my sql querying skills :) – NomadTraveler May 25 '12 at 1:33
• @Sonya, the query is large and intimidating. It feels like it is more complicated than it should be, but I cannot say for sure. I noticed that you are doing a lot of joining on sub-queries and intermediate group-bys - just seems like there is a simpler way if you think in terms of sets. Can you post the table definitions and the relationships between them? If you sit down with pen and paper, then can you write down some sort of formula in an easy way? – Leonid May 25 '12 at 3:45

You really should be more consistent with your choice of line breaks and indentation, especially in a query as large as this. Improving the readability should also make it easier to spot areas for improvement. You didn't indicate which DBMS you're using, but if it's supported, the WITH clause ("common table expressions" or "subquery factoring clause") can help reduce the number of indentation levels you have to deal with.

I feel like you're using ISNULL too much. Remember that aggregate functions like SUM ignore NULL values, so an expression like SUM(ISNULL(Result.MemberBonus,0)) is equivalent to SUM(Result.MemberBonus).

A big problem I see is that you join some tables and subqueries too early. For example, Country and Region appear to be necessary only for information and don't actually affect any calculations. Furthermore, Country and Region only depend on the TeamID. Since that's the case, instead of joining them in the inner-most subquery, join them as part of the outer query. This will shorten up the GROUP BY clauses.

The steps I would take to write this query:

1. Write a query which calculates the distance by team. The only output columns should be TeamID and TotalDistance.
2. Write a query which calculates the total Member bonus by team. The only output columns should be TeamID and TotalMemberBonus.
3. Write a query which calculates the total team bonus by team. The only output columns should be TeamID and TotalTeamBonus.
4. Join the first three queries on TeamID, along with any other information tables like Country and Region. Note that all the aggregation was done in the subqueries, so this final query should not need a GROUP BY clause.

If you follow this outline, you shouldn't need to calculate a ROW_NUMBER to sort the results. Just ORDER BY TotalDistance + TotalMemberBonus + TotalTeamBonus.

The advantage of this approach is that you can test each individual query separately to ensure that you are getting correct results at each step. It also should result in a shorter, easier-to-follow query.