Thread: 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
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
Good luck to you both! You do such a good job in this group!
Ronni
On 11/28/05, Tom Lane <tgl@sss.pgh.pa.us> wrote:
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