Alan Lawhon
lawhonac at hiwaay.net
Wed Dec 5 00:34:25 CST 2012
John: Don't know if this would be less (or more) work, but here's an idea that might work. Create a temporary column, (i.e. TEMP_1), next to the column where you want the COUNT() of total records containing a "1" value. Then UPDATE each record (in the TEMP_1 column) with a value of "1" where the corresponding record in the column you're interested in contains a "1" and a value of "NULL" if the value in the corresponding record contains a zero. Then you can do a COUNT(TEMP_1) on the TEMP_1 column. After you've got the COUNT(TEMP_1) value, DROP the [temporary] TEMP_1 field. If there are multiple columns where you want to do this, create TEMP_2, TEMP_3, TEMP_n fields and repeat the steps. If this is a one time operation, do it manually. If this is going to be a recurring task, write a script that automates the process. Alan C. Lawhon -----Original Message----- From: dba-sqlserver-bounces at databaseadvisors.com [mailto:dba-sqlserver-bounces at databaseadvisors.com] On Behalf Of jwcolby Sent: Tuesday, December 04, 2012 8:09 AM To: Sqlserver-Dba; Access Developers discussion and problem solving Subject: [dba-SQLServer] Count multiple columns I am doing counts for a set of columns. The values in the column are either 1 or 0 signifying true or false. If I just do a count() as xyz all the columns give a count equal to the total number of records. I need to do a count of a value of 1. I discovered that if I change one of the 0 values to a NULL then the record with Null does not get counted. Thus I could go through the table updating each field which uses this method = null where '0'. Obviously this is a lot of work I would like to avoid. However it might be faster(?) to do the count? My preference however is to somehow tell the count() of each column to only count values of '1'. Is this possible in TSQL? -- John W. Colby Colby Consulting Reality is what refuses to go away when you do not believe in it _______________________________________________ dba-SQLServer mailing list dba-SQLServer at databaseadvisors.com http://databaseadvisors.com/mailman/listinfo/dba-sqlserver http://www.databaseadvisors.com