From: Martijn Tonies
Date: July 2 2004 1:45pm
Subject: Re: How to get count(id) when count is zero?
List-Archive: http://lists.mysql.com/mysql/168341
Message-Id: <028e01c4603a$d17ee320$0a02a8c0@martijn>
MIME-Version: 1.0
Content-Type: text/plain;
	charset="iso-8859-1"
Content-Transfer-Encoding: 7bit

Hi,

> It sounds like a simple query but I have spent quite a few hours already,
and still do not have a solution. Would greatly appreciate your response.
>
> Here is what I am trying to do:
> -------------------------------
> create table C (cId  tinyint(4) NOT NULL);
> insert into C values (1), (2), (3), (4), (5);
>
> create table D (id tinyint(4) NOT NULL, catId  tinyint(4) NOT NULL);
> insert into D values (1,2), (2,2), (3,2), (4,1), (5,1);
>
> select C.cId, count(C.cId) from C, D where C.cId = D.catId group by C.cid;
>
> This returns:
> -------------
>
> | cId | count(C.cId) |
> +-----+--------------+
> |   1 |            2 |
> |   2 |            3 |
> +-----+--------------+
> 2 rows in set (0.01 sec)
>
> ----------------------------
> What I would like to see is:
> ----------------------------
>
> | cId | count(C.cId) |
> +-----+--------------+
> |   1 |            2 |
> |   2 |            3 |
> |   3 |            0 |
> |   4 |            0 |
> |   5 |            0 |

I'm unsure why Count(C.cID) should be 0 if you're
counting C.CIDs...

This returns what you want, but it counts catID in D:
select C.cId, count(D.catId)
from C left outer join D on C.cId = D.catId
group by C.cid

With regards,

Martijn Tonies
Database Workbench - developer tool for InterBase, Firebird, MySQL & MS SQL
Server.
Upscene Productions
http://www.upscene.com