Обсуждение: sub select as a data column

I am having trouble coming up with words to describe exactly what I need so searching for a solution is getting frustrating!  If this has been answered twelve hundred times - please forgive me!

I want to do in postgresql what I do in oracle:


select year report_year,
         sum(amount),
         sum(select amount from my_table where year <= report_year)
from my_table
group by report_year;


I see lots of stuff on subselect in the from clause - but how do you subselect as a data column?

Thanks for the help - it is SEVERLY appreciated!
Ronni

you were close, try the following (untesting).  You need the alias on the outer my_table for this to work


select year report_year,
          sum(amount),
          sum(select amount from my_table where year <= a.year)
from my_table a
group by report_year;


---------- Original Message -----------
From: Phong & Ronni Bounmixay <bounmixay@gmail.com>
To: pgsql-general@postgresql.org
Sent: Mon, 28 Nov 2005 13:18:41 -0700
Subject: [GENERAL] sub select as a data column

> I am having trouble coming up with words to describe exactly what I need so
> searching for a solution is getting frustrating!  If this has been answered
> twelve hundred times - please forgive me!
>
> I want to do in postgresql what I do in oracle:
>
> select year report_year,
>          sum(amount),
>          sum(select amount from my_table where year <= report_year)
> from my_table
> group by report_year;
>
> I see lots of stuff on subselect in the from clause - but how do you
> subselect as a data column?
>
> Thanks for the help - it is SEVERLY appreciated!
> Ronni
------- End of Original Message -------

Phong & Ronni Bounmixay <bounmixay@gmail.com> writes:
> I want to do in postgresql what I do in oracle:

> select year report_year,
>          sum(amount),
>          sum(select amount from my_table where year <= report_year)
> from my_table
> group by report_year;

That doesn't really work in Oracle does it?  It violates the SQL spec
in at least three ways.  Try something like

select year as report_year,
       sum(amount),
       sum((select amount from my_table b where b.year <= a.year))
from my_table a
group by year;

(which should work in Oracle too, or any other SQL-spec-compliant
database).  Note the extra parentheses ... they're not optional.

            regards, tom lane

You both are SO right!  I sent that off and tried a couple of things and realized I was close but not close enough.  I really appreciate the kind responses.  Sometimes it's so hard to ask a question without feeling so foolish and then feeling worse when the answers are sarcastic.  Thank you!

Good luck to you both!  You do such a good job in this group!
Ronni