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(at)yahoo(dot)com]
To: Raimon Fernandez [mailto:coder(at)montx(dot)com],
pgsql-novice(at)postgresql(dot)org
Sent: Fri, 18 May 2007 17:57:20 +0200
Subject:
Re: [NOVICE] aggregate function ?

>
> --- Raimon Fernandez <coder(at)montx(dot)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.
>
>