Professional Web Applications Themes

Return zero with Count and a Where filter? - Microsoft SQL / MS SQL Server

How can COUNT return zero values if I have a Where condition? For example, the following query when run against the authors table in the pubs database.... SELECT au_lname, Count(au_lname) FROM pubs..authors WHERE RIGHT( RTRIM(au_lname) , 1) = 'e' GROUP BY au_lname will return the following data... au_lname AuthorCount ---------- ----------- DeFrance 1 Greene 1 White 1 However, I would also like the remaining authors that don't match this condition to be listed with a value of zero. For example... au_lname AuthorCount ---------- ----------- DeFrance 1 Greene 1 White 1 Carson 0 O'Leary 0 .... Thanks you for any help....

  1. #1

    Default Return zero with Count and a Where filter?

    How can COUNT return zero values if I have a Where condition?

    For example, the following query when run against the authors table in the
    pubs database....

    SELECT au_lname, Count(au_lname) FROM pubs..authors
    WHERE RIGHT( RTRIM(au_lname) , 1) = 'e'
    GROUP BY au_lname

    will return the following data...

    au_lname AuthorCount
    ---------- -----------
    DeFrance 1
    Greene 1
    White 1

    However, I would also like the remaining authors that don't match this
    condition to be listed with a value of zero. For example...

    au_lname AuthorCount
    ---------- -----------
    DeFrance 1
    Greene 1
    White 1
    Carson 0
    O'Leary 0
    ....

    Thanks you for any help.


    Cipher Guest

  2. #2

    Default Re: Return zero with Count and a Where filter?

    SELECT au_lname, sum(case when RIGHT( RTRIM(au_lname) , 1) = 'e' then 1 else
    0 end) cnt
    FROM pubs..authors
    GROUP BY au_lname

    --
    -oj
    RAC v2.2 & QALite!
    http://www.rac4sql.net


    "Cipher" <cc.com> wrote in message
    news:phx.gbl... 


    oj Guest

  3. #3

    Default Re: Return zero with Count and a Where filter?

    SELECT au_lname,
    COUNT(CASE RIGHT( RTRIM(au_lname) , 1) WHEN 'e' THEN 1 END) AS AuthorCount
    FROM pubs..authors
    GROUP BY au_lname

    SQLServer also has a proprietary GROUP BY ALL clause which does this but in
    your case it doesn't give much advantage over the standard SQL version
    above.

    SELECT au_lname, COUNT(au_lname) AS AuthorCount
    FROM pubs..authors
    WHERE RIGHT( RTRIM(au_lname) , 1) = 'e'
    GROUP BY ALL au_lname

    --
    David Portas
    ------------
    Please reply only to the newsgroup
    --


    David Guest

  4. #4

    Default Re: Return zero with Count and a Where filter?

    Have you looked at the "group by ALL" plan...double whamma for the implicit
    'union'.

    "
    (22 row(s) affected)

    Warning: Null value is eliminated by an aggregate or other SET operation.
    Table 'authors'. Scan count 2, logical reads 2, physical reads 0, read-ahead
    reads 0.

    "

    --
    -oj
    RAC v2.2 & QALite!
    http://www.rac4sql.net


    "David Portas" <org> wrote in message
    news:phx.gbl... 
    AuthorCount 
    in 


    oj Guest

  5. #5

    Default Re: Return zero with Count and a Where filter?

    select a.au_lname, Coalesce(b.counts,0)
    from pubs..authors a
    left join (SELECT au_lname, Count(au_lname) counts FROM pubs..authors
    WHERE RIGHT( RTRIM(au_lname) , 1) = 'e'
    GROUP BY au_lname) as b
    on a.au_lname = b.au_lname

    "Cipher" <cc.com> wrote in message
    news:phx.gbl... 


    HSalim Guest

Similar Threads

  1. #40499 [NEW]: filter sapi does not register any highlightning filter
    By php at henke37 dot cjb dot net in forum PHP Bugs
    Replies: 0
    Last Post: February 15th, 06:31 PM
  2. Return count() with the rows
    By pengypenguin@gmail.com in forum MySQL
    Replies: 1
    Last Post: September 14th, 04:15 PM

Bookmarks

Posting Permissions

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

1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 20 21 22 23 24 25 26 27 28 29 30 31 32 33 34 35 36 37 38 39 40 41 42 43 44 45 46 47 48 49 50 51 52 53 54 55 56 57 58 59 60 61 62 63 64 65 66 67 68 69 70 71 72 73 74 75 76 77 78 79 80 81 82 83 84 85 86 87 88 89 90 91 92 93 94 95 96 97 98 99 100 101 102 103 104 105 106 107 108 109 110 111 112 113 114 115 116 117 118 119 120 121 122 123 124 125 126 127 128 129 130 131 132 133 134 135 136 137 138 139