From: "Greg Patnude" <gpatnude@hotmail.com>
Subject: Re: determining how many products are in how many categories
Date: Wed, 11 Feb 2004 15:43:37 -0800
Organization: Hub.Org Networking Services
Lines: 76
Message-ID: <c0eep7$kk8$1@news.hub.org>
References: <40266D61.3070304@zara.6.isreserved.com>
To: pgsql-sql@postgresql.org

You might be better off with something more like this:

SELECT COUNT(P.*) AS p_count, P.p_product_category_id FROM p_product P
WHERE P.p_product_category_id IN (SELECT DISTINCT id FROM
p_product_category) GROUP BY P.p_product_category_id;

obviously tailored to YOUR schema... not mine...
-- 
Greg Patnude / The Digital Demention
2916 East Upper Hayden Lake Road
Hayden Lake, ID 83835
(208) 762-0762

"David Garamond" <lists@zara.6.isreserved.com> wrote in message
news:40266D61.3070304@zara.6.isreserved.com...
> # product table (simplified):
> create table p (
>    id char(22) not null primary key,
>    name text,
>    desc text
> );
>
> # product category table (simpl.):
> create table pc (
>    id char(22) not null primary key,
>    name text,
>    desc text
> );
>
> # table that maps products into categories:
> create table p_pc (
>    id char(22) not null primary key,
>    pid char(22) not null references p(id),
>    pcid char(22) not null references pc(id)
> );
> create index i_ppc_pid on p_pc(pid);
> create index i_ppc_pcid on p_pc(pcid);
> create unique index i_ppc_pid_pcid on p_pc(pid, pcid);
>
> There are about 50k rows in p, 40k in pc, and 140k in p_pc. Most
> products are only placed in 1 category, some in 2, fewer in 3, and fewer
> stills in 4, 5, 6 categories.
>
> I want to know how many products are placed in 1 category, how many in
> 2, and so on. Here's my query:
>
> select count(pid),num_cat from (
>    select pid,count(cid) as num_cat
>    from ppc group by pid) as f
> group by num_cat;
>
> A sample output (4000 products are categorized in 5 different places,
> 4998 in 4, and so on):
>
>   count | num_cat
> -------+---------
>    4000 |       5
>    4998 |       4
>    7502 |       3
>   10001 |       2
>   17499 |       1
> (5 rows)
>
> However, this query is taking about 2.5 minutes. Any idea on how to make
> it faster?
>
> -- 
> dave
>
> ---------------------------(end of broadcast)---------------------------
> TIP 5: Have you checked our extensive FAQ?
>
>                http://www.postgresql.org/docs/faqs/FAQ.html
>