Re: Correlated Subquery and calculated column non-functional

Поиск
Список
Период
Сортировка
Искать

Re: Correlated Subquery and calculated column non-functional

От:
Richard Broersma <richard.broersma@gmail.com>
Дата:
On Fri, Oct 30, 2009 at 10:42 AM, Raymond O'Donnell  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

Re: Correlated Subquery and calculated column non-functional

От:
Raymond O'Donnell <rod@iol.ie>
Дата:
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

Correlated Subquery and calculated column non-functional

От:
The Frog <mr.frog.to.you@googlemail.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

Re: Correlated Subquery and calculated column non-functional

От:
Thomas Kellerer <spam_eater@gmx.net>
Дата:
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
FAQ