From: James Cloos <cloos@jhcloos.com>
To: pgsql-sql@postgresql.org
Cc: Frank Bax <fbax@sympatico.ca>
Subject: Re: counting related rows
Date: Sat, 09 Oct 2010 03:49:12 -0400
Message-ID: <m3tykverfz.fsf@carbon.jhcloos.org> (raw)
In-Reply-To: <BLU0-SMTP965F0BF4A84C81E4C7400BAC500@phx.gbl>
References: <m3r5g0filx.fsf@carbon.jhcloos.org>
<BLU0-SMTP965F0BF4A84C81E4C7400BAC500@phx.gbl>
>>>>> "FB" == Frank Bax <fbax@sympatico.ca> writes:
FB> It would help if you provided:
FB> a) statements to create sample data
FB> b) expected results from sample data
FB> Does this do what you want?
FB> select * from m, (SELECT count(*) AS nch FROM m WHERE o = (SELECT o
FB> FROM m WHERE id=30016) AND name ILIKE (SELECT name || '/%' FROM m
FB> WHERE id=30016)) om;
That is almost right, except that it uses id=30016's nch value for every
row in the result, rather than computing each row's own nch.
As an example:
create TABLE m ( id integer primary key, o integer, name text,
f1 integer, f2 integer, f3 integer);
insert into m values (1, 3, 'a', 0, 1, 1);
insert into m values (2, 3, 'a/short', 1, 0, 1);
insert into m values (3, 3, 'a/short/path', 1, 0, 0);
insert into m values (4, 4, 'nothing', 0, 0, 1);
insert into m values (5, 2, 'nothing', 0, 1, 0);
insert into m values (6, 2, 'nothing/of', 1, 0, 0);
insert into m values (7, 2, 'nothing/of/value', 0, 0, 0);
The select should result in something like:
id | o | name | f1 | f2 | f3 | nch
----+---+------------------+----+----+----+-----
1 | 3 | a | 0 | 1 | 1 | 2
2 | 3 | a/short | 1 | 0 | 1 | 1
3 | 3 | a/short/path | 1 | 0 | 0 | 0
4 | 4 | nothing | 0 | 0 | 1 | 0
5 | 2 | nothing | 0 | 1 | 0 | 2
6 | 2 | nothing/of | 1 | 0 | 0 | 1
7 | 2 | nothing/of/value | 0 | 0 | 0 | 0
since rows 2 and 3 are children of row 1, row 3 is also a child of
row 2, rows 6 and 7 are children of row 5 and row 7 is also a child
of row 6.
-JimC
--
James Cloos <cloos@jhcloos.com> OpenPGP: 1024D/ED7DAEA6
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: cloos@jhcloos.com, fbax@sympatico.ca
Subject: Re: counting related rows
In-Reply-To: <m3tykverfz.fsf@carbon.jhcloos.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