Results 1 to 8 of 8
  1. #1
    Join Date
    Nov 2003
    Location
    UK
    Posts
    10

    Question Unanswered: Query help required!

    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. #2
    Join Date
    Jun 2003
    Location
    Ohio
    Posts
    12,592
    Provided Answers: 1
    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. #3
    Join Date
    Nov 2003
    Location
    UK
    Posts
    10

    Thumbs down

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

    REally appreciate your help

    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. #4
    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. #5
    Join Date
    Jun 2003
    Location
    Ohio
    Posts
    12,592
    Provided Answers: 1
    Sorry jayman. My bad.

    Try this method instead:

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

    Karolyn, please read the original post more carfully.

    blindman

  6. #6
    Join Date
    Nov 2003
    Location
    FRANCE
    Posts
    393
    misread Columns and fields...

  7. #7
    Join Date
    Nov 2003
    Location
    UK
    Posts
    10

    Thumbs up Yippee - Sorted

    cheers blindman, your too cool for school!

    jayman

    Originally posted by blindman
    Sorry jayman. My bad.

    Try this method instead:

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

    Karolyn, please read the original post more carfully.

    blindman

  8. #8
    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
    Howabout this ??

    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
  •