From: | Raimon Fernandez <coder(at)montx(dot)com> |
---|---|
To: | pgsql-novice(at)postgresql(dot)org |
Subject: | Re: aggregate function ? |
Date: | 2007-05-16 12:43:59 |
Message-ID: | 04CD9587-E5C6-46DA-B986-0A3959A0632A@montx.com |
Views: | Raw Message | Whole Thread | Download mbox | Resend email |
Thread: | |
Lists: | pgsql-novice |
On 16/05/2007, at 14:03, Richard Broersma Jr wrote:
> --- Raimon Fernandez <coder(at)montx(dot)com> wrote:
>
>>
>> yes, what I want really is the Sum(b.value_d) - sum(b.value_h)
>> accumulate
>>
>> will be faster a function for this that this SELECT ... ?
>
> Using a custom function could be comparable to a pure SQL statement
> in performance. However,
> sometimes function like this can hide details of the query from the
> PostgreSQL optimizer. If this
> happens you will see poor query performance.
ok,
>> GlobalGest=# SELECT A.oid, A.concepte, A.deure, A.haver
> --Your are missing a comma here ^
thanks !
>> (sum( B.deure )-sum(B.haver)) AS value_sum
>> FROM assentaments AS A
>> INNER JOIN assentaments AS B
>> ON A.oid <= B.oid
>> WHERE A.numero=11189
>> GROUP BY A.oid, A.concepte, A.deure, A.haver
>> ORDER BY A.oid;
>> ERROR: schema "a" does not exist
>
> What version of PostgreSQL are you using?
8.2 on OS X
>
> Your version of the query will need to look like this:
>
> SELECT A1.oid, A1.concepte, A1.deure, A1.haver,
> (sum( A2.deure )-sum(A3.haver)) AS value_sum
> FROM Assentaments AS A1
> INNER JOIN Assentaments AS A2
> ON A1.oid <= A2.oid
> INNER JOIN Assentaments AS A3
> ON A1.oid <= A3.oid
> WHERE A1.numero=11189
> GROUP BY A1.oid, A1.concepte, A1.deure, A1.haver
> ORDER BY A1.oid;
ok,
GlobalGest=# SELECT count(*) FROM assentaments WHERE numero=11189;
+-------+
| count |
+-------+
| 10 |
+-------+
1 rows in set (0.00 sec)
GlobalGest=#
but If I send the code that you send me, it takes too much .... well,
more than 20 minutes and still waiting ...
regards,
raimon
From | Date | Subject | |
---|---|---|---|
Next Message | Richard Broersma Jr | 2007-05-16 13:01:46 | Re: aggregate function ? |
Previous Message | Richard Broersma Jr | 2007-05-16 12:03:06 | Re: aggregate function ? |