Re: Correlated Subquery and calculated column non-functional - Mailing list pgsql-general

The Frog wrote on 30.10.2009 11:07:
> 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?

You can't use a column alias as reference for other expressions inside the same statement (I don't think that is
differentin Oracle or MySQL) 

Btw: your statement will be horribly in-efficient as the select count(*) will be execute for *every* row from the main
query. 

If I understand your statement correctly, you can get rid of the "sub-select" completely with Postgres 8.4

SELECT  product.manufacturer,
        product.brand,
        SUM(sales.qtysold * sales.unitprice) as turnover,
        rank() over (partition by manufacturer, brand order by SUM(sales.qtysold * sales.unitprice)) as rank
FROM cube_sales.sales
     INNER JOIN cube_sales.product ON sales.productid = product.productid
GROUP BY product.manufacturer,
         product.brand;

(Not tested)

Thomas