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