Received: from maia.hub.org (maia-2.hub.org [200.46.204.251]) by mail.postgresql.org (Postfix) with ESMTP id EFF401337B78 for ; Sat, 9 Oct 2010 04:52:04 -0300 (ADT) Received: from mail.postgresql.org ([200.46.204.86]) by maia.hub.org (mx1.hub.org [200.46.204.251]) (amavisd-maia, port 10024) with ESMTP id 06050-01 for ; Sat, 9 Oct 2010 07:51:56 +0000 (UTC) X-Greylist: from auto-whitelisted by SQLgrey-1.7.6 Received: from eagle.jhcloos.com (eagle.jhcloos.com [207.210.242.212]) by mail.postgresql.org (Postfix) with ESMTP id 92A881337B3E for ; Sat, 9 Oct 2010 04:51:56 -0300 (ADT) Received: by eagle.jhcloos.com (Postfix, from userid 10) id 10FD9400BD; Sat, 9 Oct 2010 07:51:31 +0000 (UTC) DKIM-Signature: v=1; a=rsa-sha256; c=relaxed/relaxed; d=jhcloos.com; s=eagle; t=1286610715; bh=glOrhPVm/rQbxRYX6Fng65Dm5Jbg1eds8Q2wrnk2RKo=; h=From:To:Cc:Subject:In-Reply-To:References:Date:Message-ID: MIME-Version:Content-Type; b=IgkhPSE8jsjcshSAPNPlOzL2jvE/hbC4VWSzShqvNeGm+UOcAu6gpmtnpXwo3ZdN1 43aeEJacU+FAW09melBoepd0OXFp72KtSUui7s9AMl3ZL0A5aWFSBdcr4A18x6Dt9B ZVLtClPiXuBBG1T8t+cYKbYB/7fNZixs9C4tvQXk= Received: from carbon.jhcloos.org (localhost [127.0.0.1]) by carbon.jhcloos.org (Postfix) with ESMTP id 39EF71DDF38; Sat, 9 Oct 2010 07:49:13 +0000 (UTC) From: James Cloos To: pgsql-sql@postgresql.org Cc: Frank Bax Subject: Re: counting related rows In-Reply-To: (Frank Bax's message of "Fri, 8 Oct 2010 19:09:56 -0400") References: User-Agent: Gnus/5.110011 (No Gnus v0.11) Emacs/24.0.50 (gnu/linux) Face: iVBORw0KGgoAAAANSUhEUgAAABAAAAAQCAYAAAAf8/9hAAAABHNCSVQICAgIfAhkiAAAAI1J REFUOE+lU9ESgCAIg64P1y+ngUdxhl5H8wFbbM0OmUiEhKkCYaZThXCo6KE5sCbA1DDX3genvO4d eBQgEMaM5qy6uWk4SfBYfdu9jvBN9nSVDOKRtwb+I3epboOsOX5pZbJNsBJFvmQQ05YMfieIBnYX FK2N6dOawd97r/e8RjkTLzmMsiVgrAoEugtviCM3v2WzjgAAAABJRU5ErkJggg== Copyright: Copyright 2009 James Cloos OpenPGP: ED7DAEA6; url=http://jhcloos.com/public_key/0xED7DAEA6.asc OpenPGP-Fingerprint: E9E9 F828 61A4 6EA9 0F2B 63E7 997A 9F17 ED7D AEA6 Date: Sat, 09 Oct 2010 03:49:12 -0400 Message-ID: Lines: 47 MIME-Version: 1.0 Content-Type: text/plain X-Hashcash: 1:30:101009:pgsql-sql@postgresql.org::G1M076bodDvTVcSs:000000000000000000000000000000000000UikoM X-Hashcash: 1:30:101009:fbax@sympatico.ca::36ph4BcujN7E73nH:0000000000000000000000000000000000000000000KjMh+ X-Virus-Scanned: Maia Mailguard 1.0.1 X-Spam-Status: No, hits=-1.91 tagged_above=-5 required=5 tests=BAYES_00=-1.9, T_RP_MATCHES_RCVD=-0.01 X-Spam-Level: X-Archive-Number: 201010/28 X-Sequence-Number: 34679 >>>>> "FB" == Frank Bax 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 OpenPGP: 1024D/ED7DAEA6