Re: [NOVICE] aggregate function ?

[coder(at)montx(dot)com]

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
>