Skip site navigation (1) Skip section navigation (2)

Re: aggregate function ?

From: Raimon Fernandez <coder(at)montx(dot)com>
To: pgsql-novice(at)postgresql(dot)org
Subject: Re: aggregate function ?
Date: 2007-05-16 13:34:57
Message-ID: 6D8716C0-A65A-42B4-8461-407E74FE258A@montx.com (view raw or flat)
Thread:
Lists: pgsql-novice
On 16/05/2007, at 15:01, Richard Broersma Jr wrote:

>
> --- Raimon Fernandez <coder(at)montx(dot)com> wrote:
>
>> but If I send the code that you send me, it takes too much .... well,
>> more than 20 minutes and still waiting ...
>
> just for testing try this version again so see if it preforms much  
> better, then see if the summing
> results are correct according to what you need.
>
>     SELECT A1.oid, A1.concepte, A1.deure, A1.haver,
>            sum( COALESCE( A1.deure, 0 ) - COALESCE( A2.haver, 0 ))  
> AS value_sum
>       FROM Assentaments AS A1
> INNER JOIN Assentaments AS A2
>         ON A1.oid <= A2.oid
>      WHERE A1.numero=11189
>   GROUP BY A1.oid, A1.concepte, A1.deure, A1.haver
>   ORDER BY A1.oid;



ok, here are the results:

now I'm using PGadminIII ...


180108;"fra.reg.prop. L´HOSPITALET-4";381.19;0.00;-523787081.74
180109;"fra.reg.prop. L´HOSPITALET-4";121.54;0.00;-728296890.68
180110;"fra.reg.prop. L´HOSPITALET-4";146.82;0.00;-708385624.70
180111;"fra.reg.prop. L´HOSPITALET-4";0.00;1746.83;-824026048.58
181496;"fra.reg.prop. L´HOSPITALET-4";140.46;0.00;-712741517.72
181497;"fra.reg.prop. L´HOSPITALET-4";146.36;0.00;-708102800.88
181498;"fra.reg.prop. L´HOSPITALET-4";134.11;0.00;-717734460.74
181499;"fra.reg.prop. L´HOSPITALET-4";143.75;0.00;-710155193.05
181500;"fra.reg.prop. L´HOSPITALET-4";116.05;0.00;-731934295.60
181501;"fra.reg.prop. L´HOSPITALET-4";416.55;0.00;-495668390.15


-- Executing query:
SELECT A1.oid, A1.concepte, A1.deure, A1.haver,

            sum( COALESCE( A1.deure, 0 ) - COALESCE( A2.haver, 0 ))  
AS value_sum

       FROM Assentaments AS A1

INNER JOIN Assentaments AS A2

         ON A1.oid <= A2.oid

      WHERE A1.numero=11189

   GROUP BY A1.oid, A1.concepte, A1.deure, A1.haver

   ORDER BY A1.oid;

Total query runtime: 58371 ms.
10 rows retrieved.


using postgreSQL from Navicat ...

GlobalGest=# SELECT A1.oid, A1.concepte, A1.deure, A1.haver,
            sum( COALESCE( A1.deure, 0 ) - COALESCE( A2.haver, 0 ))  
AS value_sum
       FROM Assentaments AS A1
INNER JOIN Assentaments AS A2
         ON A1.oid <= A2.oid
      WHERE A1.numero=11189
   GROUP BY A1.oid, A1.concepte, A1.deure, A1.haver
   ORDER BY A1.oid;
+--------+-------------------------------+--------+--------- 
+---------------+
| oid    | concepte                      | deure  | haver   |  
value_sum     |
+--------+-------------------------------+--------+--------- 
+---------------+
| 180108 | fra.reg.prop. L´HOSPITALET-4 | 381.19 | 0.00    |  
-523787081.74 |
| 180109 | fra.reg.prop. L´HOSPITALET-4 | 121.54 | 0.00    |  
-728296890.68 |
| 180110 | fra.reg.prop. L´HOSPITALET-4 | 146.82 | 0.00    |  
-708385624.70 |
| 180111 | fra.reg.prop. L´HOSPITALET-4 | 0.00   | 1746.83 |  
-824026048.58 |
| 181496 | fra.reg.prop. L´HOSPITALET-4 | 140.46 | 0.00    |  
-712741517.72 |
| 181497 | fra.reg.prop. L´HOSPITALET-4 | 146.36 | 0.00    |  
-708102800.88 |
| 181498 | fra.reg.prop. L´HOSPITALET-4 | 134.11 | 0.00    |  
-717734460.74 |
| 181499 | fra.reg.prop. L´HOSPITALET-4 | 143.75 | 0.00    |  
-710155193.05 |
| 181500 | fra.reg.prop. L´HOSPITALET-4 | 116.05 | 0.00    |  
-731934295.60 |
| 181501 | fra.reg.prop. L´HOSPITALET-4 | 416.55 | 0.00    |  
-495668390.15 |
+--------+-------------------------------+--------+--------- 
+---------------+
10 rows in set (49.94 sec)

GlobalGest=#

The value_sum is wrong, in the first row should be: 381.19 and go on ...



but maybe is a silly question, but I'm a really novice, i thought  
this should be really-really faster ... near 50 seconds is too much ...


regards and really thanks for your help.



In response to

Responses

pgsql-novice by date

Next:From: Richard Broersma JrDate: 2007-05-16 14:06:49
Subject: Re: aggregate function ?
Previous:From: Richard Broersma JrDate: 2007-05-16 13:01:46
Subject: Re: aggregate function ?

Privacy Policy | About PostgreSQL
Copyright © 1996-2014 The PostgreSQL Global Development Group