Results 1 to 3 of 3
  1. #1
    Join Date
    Aug 2006
    Posts
    87

    Unanswered: problem of displaying 0 when no records are found

    Hi everybody

    Have this situation wherein I have to display 0 when no records are found on the totals of each group.. tried the query below but doesn't work it only displays the result of groups which have values

    select coalesce(x.total,0) as totals, x.GR
    FROM
    (
    SELECT GR, COUNT(SocID)AS total
    FROM Soc
    WHERE DatePart(year,Enter) = '2010'
    AND GR IN('A','B','C')
    GROUP BY GR
    )as x

    result is:
    GR totals
    A 20
    B 30

    It doesn't display Gr='C' since it doesn't find any record that satisfies the condition but how could I let it display 0 for totals..

    GR totals
    A 20
    B 30
    C 0

    thanks
    alex

  2. #2
    Join Date
    Feb 2004
    Location
    One Flump in One Place
    Posts
    14,912
    Code:
    SELECT GR, SUM(CASE WHEN Enter >= '20100101' AND Enter < '20110101' THEN 1 ELSE 0 END) AS total
    FROM 	Soc 
    AND GR IN('A','B','C')
    GROUP BY GR

  3. #3
    Join Date
    Aug 2006
    Posts
    87
    Quote Originally Posted by pootle flump View Post
    Code:
    select gr, sum(case when enter >= '20100101' and enter < '20110101' then 1 else 0 end) as total
    from 	soc 
    and gr in('a','b','c')
    group by gr


    thanks so much that did the trick!!!

Posting Permissions

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