Thread: Grouped item in a subquery
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
A shorter/simpler example of what I think I'm saying:
SELECT COUNT(*) AS count, SUBSTRING(lastName FROM 1 FOR 1) AS first,
(SELECT COUNT(*)
FROM productLevelDet AS pDet
WHERE SUBSTRING(description FROM 1 FOR 1) = first) AS
prod_count
FROM customer
GROUP BY first
This COUNTS by 1st letter of surname all customers, and also counts the
number of products that have the same first letter. This errors as
'first' does not exist as a customer column.
I want to get this in one query if possible, as it will be used to
populate cells (directly) in a spreadsheet via an ODBC connection.
On Thu, 2005-06-23 at 17:01, Steve Tucknott wrote:
> 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
>
> ---------------------------(end of broadcast)---------------------------
> TIP 6: Have you searched our list archives?
>
> http://archives.postgresql.org
--
Regards,
Steve Tucknott
ReTSol Ltd
DDI 01903 828769
MOBILE 07736715772
___________________________________________________________
Yahoo! Messenger - NEW crystal clear PC to PC calling worldwide with voicemail http://uk.messenger.yahoo.com
Steve Tucknott <steve@retsol.co.uk> writes:
> A shorter/simpler example of what I think I'm saying:
> SELECT COUNT(*) AS count, SUBSTRING(lastName FROM 1 FOR 1) AS first,
> (SELECT COUNT(*)
> FROM productLevelDet AS pDet
> WHERE SUBSTRING(description FROM 1 FOR 1) = first) AS
> prod_count
> FROM customer
> GROUP BY first
I think you need an extra level of subselect:
SELECT ss.*,
(SELECT COUNT(*)
FROM productLevelDet AS pDet
WHERE SUBSTRING(description FROM 1 FOR 1) = ss.first) AS prod_count
FROM
(SELECT COUNT(*) AS count, SUBSTRING(lastName FROM 1 FOR 1) AS first,
FROM customer
GROUP BY first) ss;
regards, tom lane