Re: Grouped item in a subquery

Steve Tucknott <steve(at)retsol(dot)co(dot)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