1. Registered User
Join Date
Nov 2003
Location
UK
Posts
10

I have stored procedure that returns the sum total of the fields in a table. What I now want to do is divide this figure by the number of columns that dont contain a 0.

Is there a way of counting the number of fields in a table and filtering them to only counting non 0 valued columns?

Jay

2. World Class Flame Warrior
Join Date
Jun 2003
Location
Ohio
Posts
12,595
Divide by:
(isnull(nullif(Column1, 0), 0) + isnull(nullif(Column2, 0), 0)...isnull(nullif(ColumnN, 0), 0))

You can omit the isnull functions if you are sure your columns do not contain NULLs, but it is safest to leave it in.

blindman

3. Registered User
Join Date
Nov 2003
Location
UK
Posts
10
Thanks for the reply blindman, however this appears to sum the fields rather than count them. Have I misunderstood?

E.g If the sume of the fields is 16 and there are 6 columns the answer is 2.66. If however the 16 is made up of values from only 5 of the six columns the answer is 3.2 (this if the 6th column is a 0 value).

jayman

Originally posted by blindman
Divide by:
(isnull(nullif(Column1, 0), 0) + isnull(nullif(Column2, 0), 0)...isnull(nullif(ColumnN, 0), 0))

You can omit the isnull functions if you are sure your columns do not contain NULLs, but it is safest to leave it in.

blindman

4. Registered User
Join Date
Nov 2003
Location
FRANCE
Posts
393

## Re: Query help required!

Maybe...

Select sum(Col)/(Select count(id) From Table Where Col<>0)
From Table

5. World Class Flame Warrior
Join Date
Jun 2003
Location
Ohio
Posts
12,595

cast(cast(Column1 as bit) as int) + cast(cast(Column2 as bit) as int)...

blindman

6. Registered User
Join Date
Nov 2003
Location
FRANCE
Posts
393

7. Registered User
Join Date
Nov 2003
Location
UK
Posts
10

## Yippee - Sorted

cheers blindman, your too cool for school!

jayman

Originally posted by blindman

cast(cast(Column1 as bit) as int) + cast(cast(Column2 as bit) as int)...

blindman

8. Registered User
Join Date
Dec 2003
Posts
61

## Re: Query help required!

Originally posted by jayman
I have stored procedure that returns the sum total of the fields in a table. What I now want to do is divide this figure by the number of columns that dont contain a 0.

Is there a way of counting the number of fields in a table and filtering them to only counting non 0 valued columns?

Jay

Select
(Case When Col1 > 0 then 1 End) +
(Case When Col2 > 0 then 1 End) +
(Case When Col3 is 0 then 1 End) +
......

You'll arrive at the count of all columns which have values > 0.

#### Posting Permissions

• You may not post new threads
• You may not post replies
• You may not post attachments
• You may not edit your posts
•