Re: [GENERAL] sub-select with multiple records, columns

2017-06-19 Thread Israel Brewster
On Jun 19, 2017, at 12:29 PM, Thomas Kellerer wrote: > > Israel Brewster schrieb am 19.06.2017 um 22:17: >> SELECT >> ... >> (SELECT >> array_agg(to_json(row(notedate,username,note))) >> FROM sabrenotes >> INNER JOIN users ON author=users.id >> WHERE ticket=sabretickets.id ) notes >> FROM tickets

Re: [GENERAL] sub-select with multiple records, columns

2017-06-19 Thread David G. Johnston
On Mon, Jun 19, 2017 at 1:32 PM, David G. Johnston wrote: > On Mon, Jun 19, 2017 at 1:29 PM, Thomas Kellerer wrote: >> >> Israel Brewster schrieb am 19.06.2017 um 22:17: >>> >>> SELECT >>> ... >>> (SELECT >>> array_agg(to_json(row(notedate,username,note))) >>> FROM sabrenotes >>> INNER JOIN users

Re: [GENERAL] sub-select with multiple records, columns

2017-06-19 Thread Thomas Kellerer
Israel Brewster schrieb am 19.06.2017 um 22:17: SELECT ... (SELECT array_agg(to_json(row(notedate,username,note))) FROM sabrenotes INNER JOIN users ON author=users.id WHERE ticket=sabretickets.id ) notes FROM tickets WHERE ... The only problem with this query is that the notes aren't sorted. Of

[GENERAL] sub-select with multiple records, columns

2017-06-19 Thread Israel Brewster
I have two tables, a ticket table and a notes table, set up where each ticket can have multiple notes. I'm trying to come up with a query that returns the ticket fields as well as a field that is an array type field with the values being json-encoded note records. I've come up with the following su

Re: [GENERAL] sub select as a data column

2005-11-28 Thread Jim Buttafuoco
age --- 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

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

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

[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),  

Re: [GENERAL] sub select

2001-08-30 Thread Tod McQuillin
On Thu, 30 Aug 2001, Ben-Nes Michael wrote: > Can I: > > select *, sum_rows as ( select count(*) from table2; ) from table1; I think you need to say: select *, (select count(*) from table2) as sum_rows from table1; But this won't be a very interesting query because the sum_rows column will be

[GENERAL] sub select

2001-08-30 Thread Ben-Nes Michael
Can I: select *, sum_rows as ( select count(*) from table2; ) from table1; -- Canaan Surfing Ltd. Internet Service Providers Ben-Nes Michael - Manager Tel: 972-4-6991122 http://sites.canaan.co.il -- ---(end of broadcast)-