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:NameMonTueWedJohn SmithOFFOFFCharlie MurphyTRNRicky JamesVACVACVACTOTAL231So, 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.

Inspiring

Answered

Add/Assign value to non-null text values

Forum|Forum|16 years ago
June 15, 2009
2 replies
1054 views

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.

Advanced techniques

This topic has been closed for replies.

Correct answer -__cfSearching__-

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 ....

-

-__cfSearching__-Correct answer

Inspiring

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 ....

M

mack_

Participating Frequently

Mack

L

LumpiaAuthor

Inspiring

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?

M

mack_

Participating Frequently

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

Sign up

To post, reply, or follow discussions, please sign in with your Adobe ID.

Sign in to Adobe Community

To post, reply, or follow discussions, please sign in with your Adobe ID.

Scanning file for viruses.

This file cannot be downloaded