Q
Problem solve Get help with specific problems with your technologies, process and projects.

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.

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 last published in January 2003

Dig Deeper on Oracle and SQL

PRO+

Content

Find more PRO+ content and other member only offers, here.

Have a question for an expert?

Please add a title for your question

Get answers from a TechTarget expert on whatever's puzzling you.

You will be able to add details on the next page.

Start the conversation

Send me notifications when other members comment.

By submitting you agree to receive email from TechTarget and its partners. If you reside outside of the United States, you consent to having your personal data transferred to and processed in the United States. Privacy

Please create a username to comment.

-ADS BY GOOGLE

SearchDataManagement

SearchBusinessAnalytics

SearchSAP

SearchSQLServer

TheServerSide

SearchDataCenter

SearchContentManagement

SearchFinancialApplications

Close