Re: 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
> >
>