Re: Correlated Subquery and calculated column non-functional

On Fri, Oct 30, 2009 at 10:42 AM, Raymond O'Donnell <rod(at)iol(dot)ie> wrote:
> On 30/10/2009 10:07, The Frog wrote:
>> select
>>       product.manufacturer,
>>       product.brand,
>>       SUM(sales.qtysold * sales.unitprice) as turnover,
>>       (select count(*) from cube_sales.sales as Q WHERE SUM(sales.qtysold *
>> sales.unitprice) > turnover) + 1 as rank
>
> You can't use the alias "turnover" in the calculation as you have - you
> need to use the full expression instead, or push the calculation into a
> subquery.
>
> BTW you're also missing a closing parenthesis from that calculation.

Also you can't use aggregate functions in the WHERE clause. You'll
need to push it down to the HAVING clause.

--
Regards,
Richard Broersma Jr.

Visit the Los Angeles PostgreSQL Users Group (LAPUG)
http://pugs.postgresql.org/lapug