Results 1 to 3 of 3
  1. #1
    Join Date
    Mar 2010
    Posts
    4

    Unanswered: Get sybase to display counts as 0 if no values returned.

    Hi

    I am using ASE15 on solaris

    I have 2 tables MC and AA

    MC has mc.ID and mc.Name columns

    AA has aa.ID aa.Name and aa.Txt columns


    MC data

    1,AAA
    2,BBB
    3,CCC

    AA data

    1,AAA,blah blah blah
    2,BBB,blah blah blah
    3,BBB,blah blah blah
    4,AAA,blah blah blah
    5,AAA,blah blah blah

    I want my sql I run to output counts for all Names even if there is a no rows Likeb below

    AAA 3
    BBB 2
    CCC 0

    When I run in my sql it will show counts for AAA and BBB
    AAA 3
    BBB 2

    But not CCC as I guess there is no rows returnded from the count , is there away I can get it to display the 0 count

    My code below

    select MC.Name , count(*)as MsgChan from MC as MCA Inner join AA as LT on MC.Name = LT.Name
    group by SC.s_mcname

    Any pointers appreciated.

    Thanks

  2. #2
    Join Date
    Jan 2003
    Location
    Massachusetts
    Posts
    5,800
    Provided Answers: 11
    Does Sybase have GROUP BY ALL syntax? I know MS SQL Server has that, and it is intended for this sort of thing, but I do not know when in the evolution of Sybase/SQL Server it was introduced.

  3. #3
    Join Date
    Apr 2002
    Location
    Toronto, Canada
    Posts
    20,002
    change your join from inner to left outer, and count only the matching keys in the right table

    Code:
    SELECT mc.name 
         , COUNT(aa.name) AS MsgChan 
      FROM MC  
    LEFT OUTER
      JOIN aa  
        ON aa.name = mc.name
    GROUP 
        BY mc.name
    rudy.ca | @rudydotca
    Buy my SitePoint book: Simply SQL

Posting Permissions

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