SitePoint Sponsor

User Tag List

Results 1 to 3 of 3
  1. #1
    SitePoint Wizard Zaggs's Avatar
    Join Date
    Feb 2005
    Posts
    1,051
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Get count on all rows

    Hi Guys!

    The following query returns the number of jobs listed in each industry. However, it will only return rows that have a count bigger than 0. Is there a way I can return all rows including rows which have 0 count?

    Code:
    SELECT industries . * , count( jobs.id ) AS count
    FROM industries
    LEFT JOIN jobs ON jobs.industry = industries.id
    where jobs.status='on'
    GROUP BY industries.id
    ORDER BY industries.industry
    Thanks in advance.

  2. #2
    SitePoint Guru
    Join Date
    Jan 2004
    Location
    Uppsala, sverige
    Posts
    697
    Mentioned
    2 Post(s)
    Tagged
    1 Thread(s)
    Code:
    select industries.*,
           count(jobs.id) as count
      from industries
      left 
      join jobs 
        on jobs.industry = industries.id
       and jobs.status = 'on'
     group
        by industries.id
     order
        by industries.industry

  3. #3
    SitePoint Wizard Zaggs's Avatar
    Join Date
    Feb 2005
    Posts
    1,051
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)
    Quote Originally Posted by swampBoogie View Post
    Code:
    select industries.*,
           count(jobs.id) as count
      from industries
      left 
      join jobs 
        on jobs.industry = industries.id
       and jobs.status = 'on'
     group
        by industries.id
     order
        by industries.industry
    Perfect, that works now - thank you for your help.


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
  •