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 different in 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
--
Sent via pgsql-general mailing list (pgsql-general@xxxxxxxxxxxxxx)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-general
