Re: [NOVICE] aggregate function ?

Hello again,

Ok, at least now it's working, but I'm connected from a friend's house to the office, so the connection is slow, but it's waiting too much.

The initial Select labeled started_sum, is executing for each row, or only at the beggining ?

I believe it's executing for each row, because is getting too much time to do it, when it should execute just once, for getting the initial value for add later to each accumualte row.

thanks again,

raimon

----- Original Message -----
From: Richard Broersma Jr
[mailto:rabroersma(at)yahoo(dot)com]
To: coder(at)montx(dot)com,
pgsql-novice(at)postgresql(dot)org
Sent: Fri, 18 May 2007 21:06:02 +0200
Subject:
Re: [NOVICE] aggregate function ?

> I found the typo in the query: by the way, it seems my version of postgresql
> is more verbose with
> it error message than yours.
> ...
> ERROR: missing FROM-clause entry in subquery for table "a1"
> LINE 5: AND A1.data BETWEEN '2006-01-01' AND...
> ...
>
> anyway here is the correction:
>
>
> SELECT oid, concepte, deure, haver,
> delta_sum + ( SELECT SUM( COALESCE( A3.deure ) - COALESCE ( A3.haver
> ) )
> FROM Assentaments A3
> WHERE A3.clau_compte = '0257000000002'
> AND A3.data BETWEEN '2006-01-01' AND '2006-06-30' )
> AS starting_Sum
> --This was A1 but should be A3^
> FROM( SELECT A1.oid, A1.concepte, A1.deure, A1.haver,
> sum( COALESCE( A2.deure, 0 ) -
> COALESCE( A2.haver, 0 )) AS value_sum
> FROM Assentaments AS A1
> INNER JOIN Assentaments AS A2
> ON A1.oid >= A2.oid
> AND A1.numero = A2.numero
> WHERE A1.clau_compte = '0257000000002'
> GROUP BY A1.oid, A1.concepte, A1.deure, A1.haver
> ORDER BY A1.oid ) AS Summed_Assentaments( oid, concepte, deure, haver,
> delta_sum );
>
> >
> > and here comes the error:
> >
> > ERROR: missing FROM-clause entry in subquery for table "a1"
> > estat SQL: 42P01
> > Caràcter: 254
> >
> >
> > I can send you in a private e-mail some rows in .txt if you want to try
> ...
> >
> > And no, there's no relation between numero and compte in my table, I used
> numero because for me
> > it was easier to use only one field that use one with to options, data and
> between, but the code
> > should look like it's now.
> >
> > Sorry for the inconvenience ...
> >
> >
> >
> > thanks again !
> >
> >
> >
> >
> >
> >
> > ----- Original Message -----
> > From: Richard Broersma Jr
> > [mailto:rabroersma(at)yahoo(dot)com]
> > To: coder(at)montx(dot)com,
> > pgsql-novice(at)postgresql(dot)org
> > Sent: Fri, 18 May 2007 19:47:11 +0200
> > Subject:
> > Re: [NOVICE] aggregate function ?
> >
> >
> > >
> > > --- coder(at)montx(dot)com wrote:
> > > >
> > > > ERROR: missing FROM-clause entry in subquery for table "a1"
> > > > estat SQL: 42P01
> > > > Caràcter: 254
> > > >
> > > >
> > > > This line ' WHERE A1.numero=11189 ' must change as this:
> > > >
> > > > 'WHERE A1.compte='572000001' as the values must be from the same
> 'compte'
> > >
> > > Yes, it sounds like there is a relationship in your datamodel that I am
> not
> > > able to precieve
> > > across emails. If there is a relationship between compte and numero,
> you
> > > should be able to
> > > connect this relationship up in the SQL select query.
> > >
> > > It is hard to know what the error is being produced by. can you send the
> > > actual query that you are
> > > posting and it resulting error message.
> > > Regards,
> > > Richard
> > >
> > > ---------------------------(end of broadcast)---------------------------
> > > TIP 1: if posting/reading through Usenet, please send an appropriate
> > > subscribe-nomail command to majordomo(at)postgresql(dot)org so that your
> > > message can get through to the mailing list cleanly
> > >
> >
>
>
> ---------------------------(end of broadcast)---------------------------
> TIP 5: don't forget to increase your free space map settings
>