Results 1 to 2 of 2
  1. #1
    Join Date
    May 2004
    Posts
    14

    Unanswered: count() doesn't display zero.

    1. I have following schema
    GROUPS (group_id, name, agency_id, disabled)
    GROUPING (ID, user_id, group_id, agency_id)

    2. I have following records
    GROUPS
    (117, Technical Documentation, 104, F)
    (142, Test Group, 104, F)
    (143, ABC, 104, F)
    (144, XYZ, 104, F)
    (149, Test, 104, F)
    (150, Test2, 104, F)
    (156, My Group, 104, F)
    (160, XXX-XXX, 104, F)
    (162, InformationTechnology, 104, F)
    (163, Hello world, 104, F)

    GROUPING
    (243, 252, 117, 104)
    (245, 267, 117, 104)
    (255, 255, 117, 104)
    (243, 252, 117, 104)
    (246, 286, 117, 104)
    (270, 290, 162, 104)
    (267, 217, 160, 104)
    (269, 285, 160, 104)

    3. I wrote the following SQL to display number of users in each group
    Code:
    SELECT group_id, count(user_id) 
    from grouping 
    where agency_id='104'
    AND group_id IN (SELECT group_id from groups where disabled='F')
    group by group_id
    order by group_id
    4. And I got following output
    GROUP_ID COUNT(USER_ID)
    117 4
    160 2
    162 1

    Is there any way I can print all group id and zero in count column if there is no user in that group.

    Thanks,
    Anjib

  2. #2
    Join Date
    Nov 2003
    Posts
    2,935
    Provided Answers: 12
    Code:
    SELECT group_id, count(gr.user_id) 
    from grouping gp
      LEFT JOIN groups gr ON gr.group_id = gp.group_id and disable = 'F'
    where gp.agency_id='104'
    group by gp.group_id
    order by gp.group_id

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
  •