[GENERAL] sub select as a data column

2005-11-28 Thread Phong Ronni Bounmixay 
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

Re: [GENERAL] sub select as a data column

2005-11-28 Thread Tom Lane 
Phong  Ronni Bounmixay [EMAIL PROTECTED] 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

---(end of broadcast)---
TIP 2: Don't 'kill -9' the postmaster

Re: [GENERAL] sub select as a data column

2005-11-28 Thread Phong Ronni Bounmixay 
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!
RonniOn 11/28/05, Tom Lane [EMAIL PROTECTED] wrote:
Phong  Ronni Bounmixay [EMAIL PROTECTED] 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 specin 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 agroup 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

Re: [GENERAL] sub select as a data column

2005-11-28 Thread Jim Buttafuoco 
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 [EMAIL PROTECTED]
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 ---


---(end of broadcast)---
TIP 6: explain analyze is your friend