Re: [NOVICE] aggregate function ?

ok, se here is the table details:

-- Table: assentaments

-- DROP TABLE assentaments;

CREATE TABLE assentaments
(
numero bigint NOT NULL,
data date NOT NULL,
concepte character varying NOT NULL,
deure numeric(12,2) NOT NULL,
haver numeric(12,2) NOT NULL,
clau_compte character(13) NOT NULL,
anys smallint NOT NULL,
tipus smallint NOT NULL,
ordre character(50) NOT NULL
)
WITH OIDS;
ALTER TABLE assentaments OWNER TO postgres;

-- Index: anys

-- DROP INDEX anys;

CREATE INDEX anys
ON assentaments
USING btree
(anys);

-- Index: clau_compte

-- DROP INDEX clau_compte;

CREATE INDEX clau_compte
ON assentaments
USING btree
(clau_compte);

-- Index: data

-- DROP INDEX data;

CREATE INDEX data
ON assentaments
USING btree
(data);

-- Index: numero

-- DROP INDEX numero;

CREATE INDEX numero
ON assentaments
USING btree
(numero);

And here comes the SQL statement:

SELECT oid, concepte, deure, haver,
delta_sum + ( SELECT SUM( COALESCE( A3.deure ) - COALESCE ( A3.haver ) )
FROM Assentaments A3
WHERE A3.clau_compte = '0257000000002'
AND A1.data BETWEEN '2006-01-01' AND '2006-06-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.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
>