Correlated Subquery and calculated column non-functional

Hi Everyone,

I am having an issue with a query that I thought would be a fairly
simple matter to implement, but apparently I have done something
wrong. Can anyone point me in the right direction for a solution and
explain where I have made my mistake? The idea is to produce a rank
value for each row in the resultset.

This is the query:

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
from
cube_sales.sales INNER JOIN
cube_sales.product ON
sales.productid = product.productid
group by
product.manufacturer,
product.brand;

I am receiving a : column "turnover" does not exist
SQL state: 42703
Character: 155

I understand that there is some difference with subselects in Postgres
vs MySQL or Oracle for example, but I am out of my depth on this one.

Can anyone help?

The Frog