Re: aggregate function ? - Mailing list pgsql-novice

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@yahoo.com]
> To: coder@montx.com,
> pgsql-novice@postgresql.org
> Sent: Fri, 18 May 2007 19:47:11 +0200
> Subject:
> Re: [NOVICE] aggregate function  ?
>
>
> >
> > --- coder@montx.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@postgresql.org so that your
> >        message can get through to the mailing list cleanly
> >
>