Grouped item in a subquery - Mailing list 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