Re: [NOVICE] aggregate function ?

hello again,

well, now it's faster, maybe it was the internet ...

Now it took only 6 seconds for 3217 rows through internet, great time, but the value of the sume is wrong ...

86067;"registro prop. barcelona";0.00;3.01;-9000.00
86069;"reg.mercantil barcelona (maribel)";0.00;9.00;-9000.00
86071;"reg.mercantil barcelona (maribel)";0.00;3.00;-9000.00
86073;"reg.mercantil barcelona (maribel)";0.00;7.50;-9000.00
86075;"reg.mercantil barcelona (maribel)";0.00;29.45;-9000.00
86089;"mensajeria 19/11-21/11/02 Maribel";0.00;44.43;-9000.00
86092;"mensajeria 24/12-03/12/02 Maribel";0.00;153.11;-9000.00
...

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'

This select returns this: -9000.00, so the number is ok.

thanks again, really !

rai

----- 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 21:06:02 +0200
Subject:
Re: [NOVICE] 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
> > >
> >
>
>
> ---------------------------(end of broadcast)---------------------------
> TIP 5: don't forget to increase your free space map settings
>