Please start any new threads on our new site at https://forums.sqlteam.com. We've got lots of great SQL Server experts to answer whatever question you can come up with.

 All Forums
 General SQL Server Forums
 New to SQL Server Programming
 Adding up count of row content

Author  Topic 

VeselaApo
Posting Yak Master

114 Posts

Posted - 2011-03-28 : 18:55:55
Hi,

I have a table that looks like that:

Name Maildate_1 Maildate_2 Maildate_3 TotalNumberofMailings
John 03/01/2010 Null 04/03/2010
Mary Null Null 05/02/2009

I would like to populate the last fourth column with the sum of the count of the mailings date by row. In this way for John I will have a result 2 (Maildate_1 + Maildate_3) and for Mary I will have a result 1 (only maildate_3 is counted as the other two days are null).

Any suggestions on easy execution of this? Thanks! I am using Microsoft Sql Server Management Studio.

Skorch
Constraint Violating Yak Guru

300 Posts

Posted - 2011-03-28 : 19:53:16
Try this:


update YourTable set TotalNumberOfMailings =
case when Maildate_1 is not null then 1 else 0 end
+ case when Maildate_2 is not null then 1 else 0 end
+ case when Maildate_3 is not null then 1 else 0 end


Some days you're the dog, and some days you're the fire hydrant.
Go to Top of Page

sunitabeck
Master Smack Fu Yak Hacker

5155 Posts

Posted - 2011-03-28 : 20:58:56
I would add this to Skorch's posting.

1. The code is simple enough that there is really no need to add another column to store the result of that computation. You will be wasting storage. You can compute it whenever and wherever required.

2. Speaking of "whenever" (and perhaps more importantly), every time you update a row, you will need to recompute this column, so don't store that data.

Of course, if your boss puts out an edict that "there shall be a column for TotalNumberOfMailings", you will have no choice :--)
Go to Top of Page
   

- Advertisement -