Counting the number of fields that are populated in row  
Author Message
Meridian





PostPosted: Fri Dec 16 12:27:45 CST 2005 Top

SQL Server Developer >> Counting the number of fields that are populated in row

I have 2 large tables that I'm selecting off of. part of my select is
to get the number of stores visited. The table that has this
information has the store names as columns. If a store wasn't visited
there will be a null value. I need to be able to count the number of
fields that don't have the null value in order to get the desired
result. I can't use the Count() function as the select statement is
very large. and I don't want to group by all of the fields that I'm
selecting.


Does anyone know of another way to get this?

SQL Server182  
 
 
Trey





PostPosted: Fri Dec 16 12:27:45 CST 2005 Top

SQL Server Developer >> Counting the number of fields that are populated in row what data type are the store name columns?
numeric (of some sort) indicating number of visits?
bit indicating visited?
are you returning the store columns as well?


> I have 2 large tables that I'm selecting off of. part of my select is
> to get the number of stores visited. The table that has this
> information has the store names as columns. If a store wasn't visited
> there will be a null value. I need to be able to count the number of
> fields that don't have the null value in order to get the desired
> result. I can't use the Count() function as the select statement is
> very large. and I don't want to group by all of the fields that I'm
> selecting.
>
>
> Does anyone know of another way to get this?
>
 
 
Aaron





PostPosted: Fri Dec 16 12:34:05 CST 2005 Top

SQL Server Developer >> Counting the number of fields that are populated in row > to get the number of stores visited. The table that has this
> information has the store names as columns.

Egads. So if you add a store, you change your schema? That's really not
how it should work. The store names are *data* not *metadata*...

> If a store wasn't visited
> there will be a null value. I need to be able to count the number of
> fields that don't have the null value in order to get the desired
> result.

If this returns exactly one row, you can try this:

SELECT
col1,
col2,
NumberOfStoresVisited =
CASE WHEN Store1 = NULL THEN 0 ELSE 1 END
+ CASE WHEN Store2 = NULL THEN 0 ELSE 1 END
+ ...
+ CASE WHEN StoreN = NULL THEN 0 ELSE 1 END
FROM
table
...

Without better specs, that's about as good as I can do. Please see
http://www.aspfaq.com/5006


 
 
Aaron





PostPosted: Fri Dec 16 12:47:20 CST 2005 Top

SQL Server Developer >> Counting the number of fields that are populated in row > CASE WHEN Store1 = NULL THEN 0 ELSE 1 END

WHOA! I meant WHEN Store1 IS NULL

*smack*


 
 
Bankole





PostPosted: Fri Dec 16 13:37:04 CST 2005 Top

SQL Server Developer >> Counting the number of fields that are populated in row

run the select statement to select where your column is not null and then






> > CASE WHEN Store1 = NULL THEN 0 ELSE 1 END
>
> WHOA! I meant WHEN Store1 IS NULL
>
> *smack*
>
>
>
 
 
Aaron





PostPosted: Fri Dec 16 13:45:17 CST 2005 Top

SQL Server Developer >> Counting the number of fields that are populated in row
>
> run the select statement to select where your column is not null and then


Unfortunately, I think the OP's table looks like this:

Store1 Store2 Store3 Store4 Store5
5 12 NULL 3 1




 
 
Bankole





PostPosted: Fri Dec 16 14:07:02 CST 2005 Top

SQL Server Developer >> Counting the number of fields that are populated in row Oops!!!! Sorry thot the data was in multiple rows





> >
> > run the select statement to select where your column is not null and then

>
> Unfortunately, I think the OP's table looks like this:
>
> Store1 Store2 Store3 Store4 Store5
> 5 12 NULL 3 1
>

>
>
>