Add/Assign value to non-null text values

[Lumpia]

I have a database which stores values (schedules) for each day of the month - such as whether a person is OFF or on VAC (vacation).  So, it would look something like this:

Name	Mon	Tue	Wed
John Smith	OFF	OFF
Charlie Murphy		TRN
Ricky James	VAC	VAC	VAC
TOTAL	2	3	1

So, I need the "TOTAL" row to add up the columns where the value is non-null.  I was thinking that perhaps I could somehow assign a value of 1 to each in the columns, and then perform a sum.

Can anyone assist in a Coldfusion solution or SQL Server 2005 solution?  Thanks.

[mack_]

select count(Mon) as total_mon, count(Tue) as total_tue, count(Wed) as total_wed
from table
group by name

Mack

[Lumpia]

Mack,

That doesn't seem to give an accurate answer.  Count(mon) just seems to return the number for all rows in the column.  So, in this case for Mon, instead of a count of 2 being returned (1 for OFF and 1 for VAC), it returns 3, as there are 3 rows.  Any ideas?  Anyone?

[mack_]

Hmm, are you sure you have NULL values there ? (maybe you have empty

strings instead of NULL)

From the documentation

(http://msdn.microsoft.com/en-us/library/ms175997.aspx) : Count(mon)

is equivalent to Count(ALL mon) and it counts the number of non-null

rows.

Mack

[Lumpia]

You were right Mack.  I did have empty strings in the database.  I did modify the other answer given as so, which solved the problem:

SUM(CASE WHEN column = '' THEN 0 ELSE -1 END)

Thanks to both of you for your help.

[-__cfSearching__- · Accepted solution]

I was thinking that perhaps I could somehow assign a value of 1 to each in the columns, and then perform a sum.

Yes, you could use a CASE statement to return 1 or 0.  Then wrap it in a SUM(..).

      SELECT SUM( CASE WHEN ColumnName IS NULL THEN 0 ELSE 1 END ) AS TotalNumber ....