Re: Grouped item in a subquery

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