Thread: Re: [NOVICE] aggregate function ?

Hi Richard,


With this Select, I get this error:

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'

I changed it but the error persist ...

Ah, finally I found the JOE CELK's puzzles SQL ... in english and I bought it, now is time to study ...

thanks !


raimon


Also the starting_sume

----- Original Message -----
From: Richard Broersma Jr
[mailto:rabroersma@yahoo.com]
To: Raimon Fernandez [mailto:coder@montx.com],
pgsql-novice@postgresql.org
Sent: Fri, 18 May 2007 17:57:20 +0200
Subject:
Re: [NOVICE] aggregate function  ?


>
> --- Raimon Fernandez <coder@montx.com> wrote:
> >
> > compte: 572 0000 01
> > date init: 15/01/2007
> > date end: 30/01/2007
> >
> > The initial sum, would be the compte field plus a date init of the
> > year, in that case, 01/01/2007 to the date init, 15/01/2007. Here
> > we'll get the SUM of the two values (deure-haver), and this would be
> > the initial value of the sum_value to add, so the code would be:
> >
> > SELECT SUM(deure)-SUM(haver) FROM assentaments WHERE
> > assentaments.compte='572000001' and assentaments.data >='2007-01-15'
> > and assentaments.data <='2007-01-30';
>
>
> So if I understand you correctly:
>
> SELECT oid, concepte, deure, haver,
>        delta_sum + ( SELECT SUM( COALESCE( A3.deure ) - COALESCE ( A3.haver
> ) )
>                        FROM Assentaments A3
>                       WHERE A3.compte = '572000001'
>                         AND A1.data BETWEEN '2007-01-15' AND '2007-01-30' )
> AS starting_Sum
>   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.numero=11189
>       GROUP BY A1.oid, A1.concepte, A1.deure, A1.haver
>       ORDER BY A1.oid ) AS Summed_Assentaments( oid, concepte, deure, haver,
> delta_sum );
>
>
> Regards,
> Richard Broersma Jr.
>
>

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