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:

enter image description here

 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
    -- 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
        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,
                SELECT Group1.TeamID, Group1.Name, Country, Region, Minutes, Distance, MemberBonus, TeamSize 
                    --Get a sum of distance covered by the team's members. Only get the data for the active teams ( Status = 1) 
                        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
                    Team LEFT JOIN Country ON Team.fk_CountryID = Country.CountryID 
                    LEFT JOIN Region ON Team.fk_RegionID = Region.RegionID
                        TeamMember LEFT JOIN 
                            Member LEFT JOIN Activity ON Activity.fk_MemberID = Member.MemberID 
                        ON Member.MemberID = TeamMember.MemberID
                    ON Team.TeamID = TeamMember.TeamID
                        Team.Status = 1 AND Member.Disabled = 0
                    GROUP BY 
                        TeamMember.TeamID, Team.TeamID, Team.Name, Country.Name, Region.Name                                    
                        -- Get a sum of Bonus points given to the team's members.
                            TeamMember.TeamID, Member.MemberID, SUM(ISNULL(MemberBonus.BonusPoints,0)) AS MemberBonus    
                        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
                        -- Get the team size ( number of members in the team)
                        SELECT COUNT(TeamMember.TeamID) AS TeamSize,TeamID FROM TeamMember 
                        GROUP BY TeamMember.TeamID
                    ON Group1.TeamID = Group3.TeamID 
        JOIN Team ON Result.TeamID = Team.TeamID        
        GROUP BY 
            Team.TeamID, Team.Name, Result.Country, Result.Minutes, Result.Distance, Result.Region, Result.TeamSize
    (   --Get the Bonus points given to the team 
        SELECT ISNULL(TeamBonus.BonusPoints,0)AS BonusPoints, fk_TeamID  FROM TeamBonus
    ON teamRank.TeamID = teamBonusData.fk_TeamID
        -- 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'
    ON teamRank.TeamID = captainData.CapTeamID
        teamRank.TeamID, teamRank.Name, teamRank.Country, teamRank.Minutes, teamRank.Distance, teamRank.Region, teamRank.MemberBonus, captainData.OfficeLocation, teamRank.TeamSize
) myTeamRank
    myTeamRank.TeamID, myTeamRank.Name, myTeamRank.Country, myTeamRank.Minutes, myTeamRank.Distance, myTeamRank.Region, myTeamRank.OfficeLocation,TeamSize
  • 1
    \$\begingroup\$ 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/… \$\endgroup\$
    – Leonid
    Commented May 25, 2012 at 0:39
  • \$\begingroup\$ 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 :) \$\endgroup\$ Commented May 25, 2012 at 1:33
  • \$\begingroup\$ @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? \$\endgroup\$
    – Leonid
    Commented May 25, 2012 at 3:45

1 Answer 1


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.


Your Answer

By clicking “Post Your Answer”, you agree to our terms of service and acknowledge you have read our privacy policy.

Not the answer you're looking for? Browse other questions tagged or ask your own question.