Results 1 to 3 of 3
  1. #1
    Join Date
    Jun 2012
    Posts
    2

    Unanswered: NEWBIE: IFNULL Query?

    I can't get the expected results from IFNULL. I have one table with field called "query_source". I would like to select a count(*) of this column for a certain condition but if it is not met I would like the result to show as "0" e.g.

    email 10
    post 77
    person 0
    call 0

    However, the only results I can get are the non-zero ones. Here is the query I am using:
    Code:
    select query_source, IFNULL(count(*),0)as count from cases 
    where query_source in ("email","call","post","person")
    and status="open" and id=1
    group by query_source
    Can anyone suggest how I might get the output I need?

  2. #2
    Join Date
    Apr 2002
    Location
    Toronto, Canada
    Posts
    20,002
    Code:
    SELECT query_sources.query_source
         , COUNT(cases.query_source) AS count_cases 
      FROM ( SELECT 'email' AS query_source
             UNION ALL
             SELECT 'call' 
             UNION ALL
             SELECT 'post' 
             UNION ALL
             SELECT 'person' ) AS query_sources
    LEFT OUTER
      JOIN cases 
        ON cases.query_source = query_sources.query_source
       AND cases.status = 'open' 
       AND cases.id = 1
    GROUP
        BY query_sources.query_source
    rudy.ca | @rudydotca
    Buy my SitePoint book: Simply SQL

  3. #3
    Join Date
    Jun 2012
    Posts
    2
    Quote Originally Posted by r937 View Post
    Code:
    SELECT query_sources.query_source
         , COUNT(cases.query_source) AS count_cases 
      FROM ( SELECT 'email' AS query_source
             UNION ALL
             SELECT 'call' 
             UNION ALL
             SELECT 'post' 
             UNION ALL
             SELECT 'person' ) AS query_sources
    LEFT OUTER
      JOIN cases 
        ON cases.query_source = query_sources.query_source
       AND cases.status = 'open' 
       AND cases.id = 1
    GROUP
        BY query_sources.query_source
    I wasn't even close - however that is perfect! Thank you very much!

Posting Permissions

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