Correlated Subquery and calculated column non-functional

[The Frog <mr(dot)frog(dot)to(dot)you(at)googlemail(dot)com>]

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