Skip site navigation (1) Skip section navigation (2)

Grouped item in a subquery

From: Steve Tucknott <steve(at)retsol(dot)co(dot)uk>
To: PostGreSQL <pgsql-novice(at)postgresql(dot)org>
Subject: Grouped item in a subquery
Date: 2005-06-23 16:01:11
Message-ID: 1119542472.9881.55.camel@retsol1 (view raw or flat)
Thread:
Lists: pgsql-novice
What is the syntax for using a grouped 'function' result in a subquery?
I am trying to group by the first part of the 'post code' and use that
in the subquery - the code is:
I tried using the pZone in the group by and also in the subquery, but
psql told me that pZone wasn't a column. So I tried repeating the
SUBSTRING function (on addr) in the subquery and with the code below I
now get ERROR:  subquery uses ungrouped column "addr.postcode" from
outer query - which is true..... is there a way to get around this?
(The query runs if I omit the AS pZone from the select, then just
compare addr.postCode to addr2.postCode in the subquery and group by
addr.postCode - but that doesn't amalgamate by the 'post zone')

SELECT SUBSTRING(addr.postCode FROM 1 FOR POSITION (' ' IN
addr.postCode)) AS pZone,
       clFlt.description,
       COUNT(*),SUM(originalEstimate) AS orig_est,
       (SELECT SUM(claimedQty * originalCost)
           FROM sourceDetProd AS srcP2
                JOIN sourceDet AS srcD2
                     JOIN sourceHdr AS srcH2
                          JOIN customer AS cust2
                               JOIN address AS addr2
                               ON   addr2.foreignTableName = 'customer'
                               AND  addr2.foreignRecNo   = cust2.recNo
                               AND  SUBSTRING(addr2.postCode FROM 1 FOR
POSITION(' ' IN addr2.postCode)) =
                                    SUBSTRING(addr.postCode FROM 1 FOR
POSITION (' ' IN addr.postCode))
                          ON   srcH2.customerRecNo = cust2.recNo
                     ON   srcD2.sourceHdrRecNo = srcH2.recNo
                ON  srcD2.recNo = srcP2.sourceDetRecNo) AS claimed,
FROM sourceHdr AS srcH
     JOIN lookUpCodes AS clFlt
     ON   srcH.clFltLookUpCodesRecNo = clFlt.recNo
     JOIN sourceDet AS srcD
          JOIN sourceDetExtref AS srcE
          ON   srcE.foreignRecNo  = srcD.recNo
          AND  srcE.tableName  = 'sourcedet'
     ON   srcD.sourceHdrRecNo = srcH.recNo
     JOIN customer AS cust
          JOIN address AS addr
          ON   addr.foreignTableName = 'customer'
          AND  addr.foreignRecNo     = cust.recNo
     ON   srcH.customerRecNo = cust.recNo
WHERE srcE.ownerForeignTableName = 'clientbranch'
AND   srcE.ownerForeignRecNo = 1
GROUP BY pZone,clFlt.description;



-- 


Regards,

Steve Tucknott
ReTSol Ltd

DDI	01903 828769
MOBILE	07736715772


		
___________________________________________________________ 
How much free photo storage do you get? Store your holiday 
snaps for FREE with Yahoo! Photos http://uk.photos.yahoo.com

Responses

pgsql-novice by date

Next:From: Steve TucknottDate: 2005-06-23 16:45:43
Subject: Re: Grouped item in a subquery
Previous:From: Sean DavisDate: 2005-06-23 13:45:35
Subject: Re: Search function

Privacy Policy | About PostgreSQL
Copyright © 1996-2014 The PostgreSQL Global Development Group