Results 1 to 14 of 14
  1. #1
    Join Date
    Dec 2006
    Posts
    35

    Unanswered: How to show null as zero ("0") in view

    The query below works great, but I need to display the TotRight field as a zero if the result is null. Any suggestions? Thanks.

    Code:
    SELECT     C270594_admin.VoteScore2.Policy, C270594_admin.RollCallsIND.district_id AS Dist, COUNT(*) AS TotRight
    FROM         C270594_admin.RollCallsIND INNER JOIN
                          C270594_admin.VoteScore2 ON C270594_admin.RollCallsIND.RCID = C270594_admin.VoteScore2.RCID
    WHERE     (C270594_admin.VoteScore2.Vote = N'y') AND (C270594_admin.RollCallsIND.result = N'y') AND (C270594_admin.VoteScore2.Scored = 1) OR
                          (C270594_admin.VoteScore2.Vote = N'n') AND (C270594_admin.RollCallsIND.result = N'n') AND (C270594_admin.VoteScore2.Scored = 1)
    GROUP BY C270594_admin.VoteScore2.Policy, C270594_admin.RollCallsIND.district_id

  2. #2
    Join Date
    Apr 2002
    Location
    Toronto, Canada
    Posts
    20,002
    as far as i know, COUNT(*) can never be NULL

    by the way, you should simplify that WHERE clause, you gots way too many needless parentheses
    Code:
    SELECT C270594_admin.VoteScore2.Policy
         , C270594_admin.RollCallsIND.district_id AS Dist
         , COUNT(*) AS TotRight
      FROM C270594_admin.RollCallsIND 
    INNER 
      JOIN C270594_admin.VoteScore2 
        ON C270594_admin.VoteScore2.RCID = C270594_admin.RollCallsIND.RCID
     WHERE (
           C270594_admin.VoteScore2.Vote = N'y'
           ) 
       AND (
           C270594_admin.RollCallsIND.result = N'y'
           ) 
       AND (
           C270594_admin.VoteScore2.Scored = 1
           ) 
        OR (
           C270594_admin.VoteScore2.Vote = N'n'
           ) 
       AND (
           C270594_admin.RollCallsIND.result = N'n'
           ) 
       AND (
           C270594_admin.VoteScore2.Scored = 1
           )
    GROUP 
        BY C270594_admin.VoteScore2.Policy
         , C270594_admin.RollCallsIND.district_id
    rudy.ca | @rudydotca
    Buy my SitePoint book: Simply SQL

  3. #3
    Join Date
    Apr 2002
    Location
    Toronto, Canada
    Posts
    20,002
    also, you might want to use table aliases to reduce the "trees" so that the "forest" is easier to read
    Code:
    SELECT vs.Policy
         , rc.district_id AS Dist
         , COUNT(*) AS TotRight
      FROM C270594_admin.RollCallsIND  AS rc
    INNER 
      JOIN C270594_admin.VoteScore2    AS vs
        ON vs.RCID = rc.RCID
     WHERE vs.Scored = 1
       AND ( vs.Vote = N'y' AND rc.result = N'y'
          OR vs.Vote = N'n' AND rc.result = N'n' 
           ) 
    GROUP 
        BY vs.Policy
         , rc.district_id
    rudy.ca | @rudydotca
    Buy my SitePoint book: Simply SQL

  4. #4
    Join Date
    Feb 2004
    Posts
    193
    SELECT Policy
    , Dist
    , CASE WHEN TotRight IS NULL
    THEN 0
    ELSE TotRight
    END AS TotRight
    FROM (
    SELECT vs.Policy
    , rc.district_id AS Dist
    , COUNT(*) AS TotRight
    FROM C270594_admin.RollCallsIND AS rc
    INNER
    JOIN C270594_admin.VoteScore2 AS vs
    ON vs.RCID = rc.RCID
    WHERE vs.Scored = 1
    AND ( vs.Vote = N'y' AND rc.result = N'y'
    OR vs.Vote = N'n' AND rc.result = N'n'
    )
    GROUP
    BY vs.Policy
    , rc.district_id
    )
    ORDER BY Policy
    , Dist

  5. #5
    Join Date
    Feb 2004
    Posts
    193
    Actually you'll need to alias the subquery that holds the original query by R937 so put an alias like "Q1" behind the close paren ")".

    ddave

  6. #6
    Join Date
    Apr 2002
    Location
    Toronto, Canada
    Posts
    20,002
    replace this --
    Code:
    CASE WHEN TotRight IS NULL
    THEN 0
    ELSE TotRight
    END AS TotRight
    with this --
    Code:
    COALESCE(TotRight,0) AS TotRight
    but i'm still not convinced that COUNT(*) will ever return NULL

    somebody should look this up or test it, eh

    rudy.ca | @rudydotca
    Buy my SitePoint book: Simply SQL

  7. #7
    Join Date
    Feb 2004
    Posts
    193
    I think you're right. I tested it in SQL Server 2005 and a NULL record returned a zero in count(fieldname). Is it possible he is using SS 2000? Does it make a difference in this situation?

    ddave

  8. #8
    Join Date
    Feb 2004
    Posts
    193
    I was thinking he would get that warning about aggregate values not being processed when they are NULL but apparently that doesn't apply w/ count(*).

    ddave

  9. #9
    Join Date
    Dec 2006
    Posts
    35
    Wow - thanks for all the replys. I ran this (below) and got the same results as the original query. (I added a where clause to narrow the results.) I still did not get zero for policy areas with zero rightvotes.

    SELECT TOP (100) PERCENT Policy, Dist, COALESCE (TotRight, 0) AS TotRight
    FROM (SELECT vs.Policy, rc.district_id AS Dist, COUNT(*) AS TotRight
    FROM C270584_admin.RollCallsIND AS rc INNER JOIN
    C270584_admin.VoteScore2 AS vs ON vs.RCID = rc.RCID
    WHERE (vs.Scored = 1) AND (vs.Vote = N'y') AND (rc.result = N'y') OR
    (vs.Scored = 1) AND (vs.Vote = N'n') AND (rc.result = N'n')
    GROUP BY vs.Policy, rc.district_id) AS derivedtbl_1
    WHERE (Dist = N'123')
    ORDER BY Policy, Dist

  10. #10
    Join Date
    Apr 2002
    Location
    Toronto, Canada
    Posts
    20,002
    Quote Originally Posted by smacky05 View Post
    I still did not get zero for policy areas with zero rightvotes.
    because what you want is slightly different than what you originally asked for

    you showed us a query and said you wanted some column to be 0 instead of NULL, when in fact your query was not returning certain policy/district combinations

    i think what you want is a LEFT OUTER JOIN

    if this doesn't work, i have another idea...
    Code:
    SELECT rc.district_id AS Dist
         , vs.Policy
         , COUNT(vs.RCID) AS TotRight
      FROM C270594_admin.RollCallsIND  AS rc
    LEFT OUTER 
      JOIN C270594_admin.VoteScore2    AS vs
        ON vs.RCID = rc.RCID
       AND vs.Scored = 1
       AND ( vs.Vote = N'y' AND rc.result = N'y'
          OR vs.Vote = N'n' AND rc.result = N'n' 
           ) 
    GROUP 
        BY vs.Policy
         , rc.district_id
    rudy.ca | @rudydotca
    Buy my SitePoint book: Simply SQL

  11. #11
    Join Date
    Feb 2004
    Location
    One Flump in One Place
    Posts
    14,912
    Quote Originally Posted by r937 View Post
    i'm still not convinced that COUNT(*) will ever return NULL
    ISO says it won't. It, rather sensibly, returns 0 when there are 0 rows. Hooda thunk?
    Testimonial:
    pootle flump
    ur codings are working excelent.

  12. #12
    Join Date
    Feb 2004
    Location
    One Flump in One Place
    Posts
    14,912
    BTW - on the 'forest' and 'trees' front I am have been persuaded by blindman - I find aliases obfuscate rather than clarify.
    Testimonial:
    pootle flump
    ur codings are working excelent.

  13. #13
    Join Date
    Dec 2006
    Posts
    35

    This might be better?

    Thanks again for all the work on this.

    I was thinking about it last night and below is the essence of what I need. All I need help with now is a way to compare the result and vote columns and then assign a value in a new column, score. The value is either "Y" or "N" for both the result and vote columns. Where result = vote (Y/Y or N/N), a new column, score, would receive a value of 1. Where result <> vote (Y/N or N/Y), the value in the score column would be 0.

    I have been trying to do an IIF or CASE statement, but can't seem to get the syntax.

    Code:
    SELECT     C270584_admin.VoteScore2.Policy, C270584_admin.RollCallsIND.district_id AS Dist, C270584_admin.RollCallsIND.result, C270584_admin.VoteScore2.Vote
    FROM         C270584_admin.RollCallsIND INNER JOIN
                          C270584_admin.VoteScore2 ON C270584_admin.RollCallsIND.RCID = C270584_admin.VoteScore2.RCID

  14. #14
    Join Date
    Dec 2006
    Posts
    35

    I think I have it...

    This seems to do the trick. Thanks again for all the help.

    SELECT C270584_admin.VoteScore2.Policy, C270584_admin.RollCallsIND.district_id AS Dist, C270584_admin.RollCallsIND.result, C270584_admin.VoteScore2.Vote,
    CASE WHEN (result = vote) THEN 1 ELSE 0 END AS Score
    FROM C270584_admin.RollCallsIND INNER JOIN
    C270584_admin.VoteScore2 ON C270584_admin.RollCallsIND.RCID = C270584_admin.VoteScore2.RCID

Tags for this Thread

Posting Permissions

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