List:General Discussion« Previous MessageNext Message »
From:Martijn Tonies Date:July 2 2004 1:45pm
Subject:Re: How to get count(id) when count is zero?
View as plain text  
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

Thread
How to get count(id) when count is zero?Nishikant Kapoor2 Jul
  • Re: How to get count(id) when count is zero?Martijn Tonies2 Jul
  • Re: How to get count(id) when count is zero?Michael Dykman3 Jul