Received: from smtp-relay.sb.dsvr.net (sb1.ho.dsvr.net [212.69.222.8]) by postgresql.org (Postfix) with ESMTP id C09E3475C07 for ; Thu, 28 Feb 2002 13:30:10 -0500 (EST) Received: from [212.69.206.2] (helo=fullerruss.dsvr.co.uk) by smtp-relay.sb.dsvr.net with esmtp (Exim 3.11 #2) id 16gVJX-0005tq-00 for pgsql-sql@postgresql.org; Thu, 28 Feb 2002 18:30:00 +0000 Received: from there (host213-122-23-218.in-addr.btopenworld.com [213.122.23.218]) by fullerruss.dsvr.co.uk (8.11.3/8.11.1) with SMTP id g1SIU1s11290 for ; Thu, 28 Feb 2002 18:30:01 GMT Message-Id: <200202281830.g1SIU1s11290@fullerruss.dsvr.co.uk> Content-Type: text/plain; charset="iso-8859-1" From: Shane Wright Reply-To: me@shanewright.co.uk To: pgsql-sql@postgresql.org Subject: GROUPing by expressions, and subSELECTs Date: Thu, 28 Feb 2002 18:29:57 +0000 X-Mailer: KMail [version 1.3.2] MIME-Version: 1.0 Content-Transfer-Encoding: 8bit X-Archive-Number: 200202/452 X-Sequence-Number: 6873 Hi I have somewhat of a problem with what has got to be my biggest query ever. So y'all dont get put off by the query itself, here's the problem: It needs to GROUP BY the result of an expression, thats fine, I put the expression in the fields bit, and GROUP BY the expression at the end: SELECT [fields], [expression] FROM table GROUP BY [expression] Now, the problem is that I want to use the result of that expression in a subquery. So, I tried nam the result of the expression (...[expression] AS name...), but no luck. So, I recreated the expression in the subquery and specified the fields used in the expression hoping that might work: SELECT [fields], [expression], (SELECT * FROM table2 WHERE [expression]) AS mynewfield FROM table GROUP BY [expression] But, it gives this error: ERROR: Sub-SELECT uses un-GROUPed attribute [table].[field] from outer query I'm stumped, and the query is screwing with my mind already. Precalculating the result of the expression into a different isn't an option because of the structure and context of its use. Anyone got any ideas? For the brave, here's the query. I know its messy, but it's dynamically generated - optimisation comes later... SELECT iid1 AS iid, ((field1 - 1009670400) / 86400) AS period, (to_char(ROUND((COUNT(iid1))::float / ( SELECT COUNT(rid) FROM table2 AS myresponses WHERE (sid=922120971) AND (field1>=(table2.field1 - 1009670400) / 86400) AND (field1<=((table2.field1 - 1009670400) / 86400)+86400) )::float * 100.0, 2), '999.99') || '%') AS count FROM table1, table2 WHERE (nid=870428218) AND (table2.rid=table1.rid) AND (field1 >= 1009670400) AND (field1 <= 1014940800) GROUP BY ((field1 - 1009670400) / 86400), iid1 ...the names have been changed to protect the innocent... Any help appreciated, thanks Shane