From: Greg Patnude <gpatnude@hotmail.com>
To: pgsql-sql@postgresql.org
Subject: Re: determining how many products are in how many categories
Date: Wed, 11 Feb 2004 15:43:37 -0800
Message-ID: <c0eep7$kk8$1@news.hub.org> (raw)
References: <40266D61.3070304@zara.6.isreserved.com>
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
>
Reply instructions:
You may reply publicly to this message via plain-text email
using any one of the following methods:
* Reply to all the recipients using the --to and --cc options:
reply via email
To: pgsql-sql@postgresql.org
Cc: gpatnude@hotmail.com
Subject: Re: determining how many products are in how many categories
In-Reply-To: <c0eep7$kk8$1@news.hub.org>
* Save the following mbox file, import it into your mail client,
and reply-to-all from there: mbox
This inbox is served by DDX for PostgreSQL; see mirroring instructions
for how to clone and mirror all data and code used for this inbox