Results 1 to 2 of 2

Thread: retrieve even if no hits?

  1. #1
    Join Date
    Apr 2005
    Posts
    7

    retrieve even if no hits?

    Hello!

    is it possible to have the count(*) to display 0 when there is no matching hits for that n_id?

    my query looks like this but only displays the n_id and it's respective count(*) when count(*) is more than 0...

    select n_id, count(*) from tblTable
    where nSomething > nSomethingElse AND nSomething IS NOT NULL
    group by n_id

    any idaes?

  2. #2
    Join Date
    Dec 2004
    Posts
    502
    How about this:

    select distinct A.n_id, isnull(B.cnt, 0)
    from tblTable as A
    left join
    (select n_id, count(*) as cnt from tblTable
    where nSomething > nSomethingElse AND nSomething IS NOT NULL
    group by n_id) as B
    on A.n_id = B.n_id

Posting Permissions

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