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?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?
dmagoo22 wrote:
> 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?
>|||> 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|||> CASE WHEN Store1 = NULL THEN 0 ELSE 1 END
WHOA! I meant WHEN Store1 IS NULL
*smack*|||try the @.@.rowcount Function
run the select statement to select where your column is not null and then
get the @.@.rowcount
"Aaron Bertrand [SQL Server MVP]" wrote:

> WHOA! I meant WHEN Store1 IS NULL
> *smack*
>
>|||> try the @.@.rowcount Function
> run the select statement to select where your column is not null and then
> get the @.@.rowcount
Unfortunately, I think the OP's table looks like this:
Store1 Store2 Store3 Store4 Store5
5 12 NULL 3 1
So @.@.ROWCOUNT will always be 1...|||Oops!!!! Sorry thot the data was in multiple rows
"Aaron Bertrand [SQL Server MVP]" wrote:

> Unfortunately, I think the OP's table looks like this:
> Store1 Store2 Store3 Store4 Store5
> 5 12 NULL 3 1
> So @.@.ROWCOUNT will always be 1...
>
>