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
Views: Raw Message | Whole Thread | Download mbox
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

Browse pgsql-novice by date

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