Thread: 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
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. Ray. -- Raymond O'Donnell :: Galway :: Ireland rod@iol.ie
On Fri, Oct 30, 2009 at 10:42 AM, Raymond O'Donnell <rod@iol.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
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