Re: Grouped item in a subquery - Mailing list pgsql-novice

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