Summing across columns
I have an SQL database table that stores names and scores. There are multiple score columns, one for each week. If a player did not play that week, he/she is given a NULL score. If a player did play that week but did not score points, he/she is given a score of 0. The problem is finding the top ten players overall. I need to sum values across the columns, and the SUM() function appears to only sum columns. Is there any way to do this with MySQL, or any RDBMS? Thank you.

    Requires Free Membership to View

    Login

    By submitting your registration information to SearchOracle.com you agree to receive email communications from TechTarget and TechTarget partners. We encourage you to read our Privacy Policy which contains important disclosures about how we collect and use your registration and other information. If you reside outside of the United States, by submitting this registration information you consent to having your personal data transferred to and processed in the United States. Your use of SearchOracle.com is governed by our Terms of Use. You may contact us at webmaster@TechTarget.com.

First of all, congratulations on understanding the difference between NULL and zero.

The best way to do this, of course, is to normalize your table. Create a second table, give it a primary key of playerid and weekno, with one column for the score for that week. If a player did not play a particular week, there would be no row for that player that week. This structure is better in a number of ways, not least of which is that you can then use SUM().

With your structure, there's no alternative but to do the addition across each row. Here, you have to be careful not to add in a NULL, because then the row summary will be NULL. Therefore, you have to use COALESCE.

select playerno
     , coalesce(week1,0) + 
       coalesce(week2,0) + 
       coalesce(week3,0) + 
       coalesce(week4,0) + ...
         as playersum
  from scores
group
    by playerno

This is fine, as far as it goes, but you will have even more of a headache if you have to find the average score for the games that the player did play, because then you have to count the number of scores that aren't null.

select playerno
     , coalesce(week1,0) + 
       coalesce(week2,0) + 
       coalesce(week3,0) + 
       coalesce(week4,0) + ...
         as playersum
     , case when week1 is null
            then 0 else 1 end + 
       case when week2 is null
            then 0 else 1 end + 
       case when week3 is null
            then 0 else 1 end + 
       case when week4 is null
            then 0 else 1 end + ...
         as weeksplayed
     , ( coalesce(week1,0) + 
         coalesce(week2,0) + 
         coalesce(week3,0) + 
         coalesce(week4,0) + ... )
       /
       ( case when week1 is null
              then 0 else 1 end + 
         case when week2 is null
              then 0 else 1 end + 
         case when week3 is null
              then 0 else 1 end + 
         case when week4 is null
              then 0 else 1 end + ... )
           as averagescore
  from scores
 where not ( week1 is null
         and week2 is null
         and week3 is null
         and week4 is null ...)
group
    by playerno

Ewww. Anyhow, imagine you have these queries written and tucked away inside your application, all ready to roll out into production, and somebody comes along and says "Hey, let's extend the season by one more week..."

Oh, and regarding your top 10 question? See FIRST N rows, TOP N rows, LAST N rows, BOTTOM N rows... and imagine substituting the average score calculation above into the variable "foo" in some of those syntax examples.

This was first published in January 2003

Join the conversationComment

Share
Comments

    Results

    Contribute to the conversation

    All fields are required. Comments will appear at the bottom of the article.
    Back to top