Thread: count() nulls?

    #1
  1. Wiking
    Devshed Expert (3500 - 3999 posts)

    Join Date
    Sep 2000
    Location
    Sweden
    Posts
    3,608
    Rep Power
    26
    Can someone help me with this?

    Say I have these two tables

    table1
    +-----+-------+
    | id | name |
    +-----+-------+
    | 1 | name1 |
    | 2 | name2 |
    | 3 | name3 |
    | 4 | name4 |
    | 5 | name5 |
    +-----+-------+

    and table2
    +-----+-------+-----+
    | id | phone | pid |
    +-----+-------+-----+
    | 1 | 12345 | 1 |
    | 2 | 23456 | 3 |
    | 3 | 35621 | 2 |
    | 4 | 55463 | 2 |
    +-----+-------+-----+

    Then I run this query:

    select t1.name, count(t2.phone) as no
    from table1 t1, table2 t2
    where t1.id=t2.pid
    group by t1.name

    and get this result

    +-------+----+
    | name | no |
    +-------+--- +
    | name1 | 1 |
    | name2 | 2 |
    | name3 | 1 |
    +-------+----+

    which is as excpected. But how shall I write a query to get this result?

    +-------+----+
    | name | no |
    +-------+----+
    | name1 | 1 |
    | name2 | 2 |
    | name3 | 1 |
    | name4 | 0 |
    | name5 | 0 |
    +------+-----+

    ie I want to see the 'NULL's too with the value 0, kinda like the Forum-pages here at devshed where you see topic, Topic Starter, and REPLIES showing 0 when there are NULLs.

    Or should I somehow run two queries in PHP to get his result?
  2. #2
  3. No Profile Picture
    Registered User
    Devshed Newbie (0 - 499 posts)

    Join Date
    Jun 2000
    Posts
    29
    Rep Power
    0
    This looks like a case for an outer (or left)join.

    select t1.name, IFNULL(count(t2.phone), 0) as no
    from table1 t1 LEFT JOIN table2 t2
    ON t1.id=t2.pid
    group by t1.name

  4. #3
  5. Wiking
    Devshed Expert (3500 - 3999 posts)

    Join Date
    Sep 2000
    Location
    Sweden
    Posts
    3,608
    Rep Power
    26
    Thanx! I better learn those joins.....

Similar Threads

  1. How to count max appearances?
    By Stew_McGruff in forum MySQL Help
    Replies: 8
    Last Post: December 13th, 2003, 05:27 PM
  2. words count in a sentence
    By alebaba in forum C Programming
    Replies: 5
    Last Post: November 22nd, 2003, 08:54 AM
  3. count specific items in column
    By st_moose in forum MySQL Help
    Replies: 2
    Last Post: June 20th, 2003, 01:40 PM
  4. count and destinct?
    By phpnewbie3 in forum Database Management
    Replies: 1
    Last Post: February 6th, 2003, 04:58 PM
  5. Making count return 0
    By Benahimvp in forum MySQL Help
    Replies: 5
    Last Post: September 1st, 2001, 03:34 PM

IMN logo majestic logo threadwatch logo seochat tools logo