Re: counting rows in another table

From: Thomas J. Kyte <tkyte_at_us.oracle.com>
Date: Tue, 07 Mar 2000 00:22:17 GMT
Message-ID: <8a1i3l$jve$1@nnrp1.deja.com>

In article <8a1fvq$igf$1_at_nnrp1.deja.com>, jeddings_at_jeddings.com wrote:
> Here's what I'm trying to do:
>
> I have an ITEMS table:
> item_id integer not null primary key
> name varchar(100)
> cat_id integer references categories(cat_id)
>
> And naturally a CATEGORIES table:
> cat_id integer not null primary key
> name varchar(100)
>
> Every item belongs in a category, but every category doesn't
> necessarily have any items in it (yet).
>
> What I'd like to do is SELECT out a row from the CATEGORIES table
based
> on the cat_id (easy enough -- it's the primary key), but I'd also like
> to know how many rows in the ITEMS table are in this category.
>
> My first inclination was:
> SELECT cat_id, name,
> count(i.item_id) as item_count
> FROM items i, categories c
> WHERE c.cat_id = 7
> AND c.cat_id = i.cat_id
>
> This gives the desired result, with one exception: if there are no
> items in this category, the second clause (c.cat_id = i.cat_id) fails
> and returns back NO rows.
>
> How might I return back the correct value (i.e. one row with
> item_count=0) with a single SQL statement?
>
> I've tried various ways, including a SUM(DECODE(...,1,0)) which I
never
> could get to work.
>
> Any thoughts?
>

outer join is one way:

ops$tkyte_at_8i> create table c ( id int );

Table created.

ops$tkyte_at_8i> create table i ( id int ); Table created.

ops$tkyte_at_8i> insert into c values ( 1 );
ops$tkyte_at_8i> insert into c values ( 2 );
ops$tkyte_at_8i> insert into i values ( 1 );
ops$tkyte_at_8i> insert into i values ( 1 );

ops$tkyte_at_8i> select c.id, count(i.id)
  2 from c, i
  3 where c.id = i.id(+)
  4 group by c.id
  5 /

ID COUNT(I.ID)
---------- -----------

         1           2
         2           0

Or in Oracle8i, release 8.1, a correlate column:

ops$tkyte_at_8i> select c.id, ( select count(*) from i where i.id = c.id ) 2 from c
3 /

ID (SELECTCOUNT(*)FROMIWHEREI.ID=C.ID)

---------- -----------------------------------
         1                                   2
         2                                   0

> Thanks,
>
> --Jeff
>
> Sent via Deja.com http://www.deja.com/
> Before you buy.
>

Thomas Kyte                              tkyte_at_us.oracle.com

Oracle Service Industries
http://osi.oracle.com/~tkyte/index.html --
Opinions are mine and do not necessarily reflect those of Oracle Corp

Sent via Deja.com http://www.deja.com/
Before you buy. Received on Mon Mar 06 2000 - 18:22:17 CST