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.

Close