Counting the number of fields that are populated in row

16 Dec 2005 5:01 PM

dmagoo22

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?

16 Dec 2005 6:27 PM

Trey Walpole

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:
Show quote

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

16 Dec 2005 6:34 PM

Aaron Bertrand [SQL Server MVP]

> 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

16 Dec 2005 6:47 PM

Aaron Bertrand [SQL Server MVP]

> CASE WHEN Store1 = NULL THEN 0 ELSE 1 END

WHOA! I meant WHEN Store1 IS NULL

*smack*

16 Dec 2005 7:37 PM

Bankole

try the @@rowcount Function

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

Show quote

"Aaron Bertrand [SQL Server MVP]" wrote:

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

16 Dec 2005 7:45 PM

Aaron Bertrand [SQL Server MVP]

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

16 Dec 2005 8:07 PM

Bankole

Oops!!!! Sorry thot the data was in multiple rows

Show quote

"Aaron Bertrand [SQL Server MVP]" wrote:

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