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 14:31:38
Message-ID: 451A8066-E35E-449A-A522-8C951DA9540C@montx.com (view raw or flat)
Thread:
Lists: pgsql-novice
hi again ...

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
        AND A1.numero = A2.numero  --this should only preform a sum  
on a specify numero
      WHERE A1.numero=11189
   GROUP BY A1.oid, A1.concepte, A1.deure, A1.haver
   ORDER BY A1.oid;

GlobalGest-# ;
+--------+-------------------------------+--------+--------- 
+-----------+
| oid    | concepte                      | deure  | haver   |  
value_sum |
+--------+-------------------------------+--------+--------- 
+-----------+
| 180108 | fra.reg.prop. L´HOSPITALET-4 | 381.19 | 0.00    | 2065.07   |
| 180109 | fra.reg.prop. L´HOSPITALET-4 | 121.54 | 0.00    | -652.97   |
| 180110 | fra.reg.prop. L´HOSPITALET-4 | 146.82 | 0.00    | -572.27   |
| 180111 | fra.reg.prop. L´HOSPITALET-4 | 0.00   | 1746.83 | -1746.83  |
| 181496 | fra.reg.prop. L´HOSPITALET-4 | 140.46 | 0.00    | 842.76    |
| 181497 | fra.reg.prop. L´HOSPITALET-4 | 146.36 | 0.00    | 731.80    |
| 181498 | fra.reg.prop. L´HOSPITALET-4 | 134.11 | 0.00    | 536.44    |
| 181499 | fra.reg.prop. L´HOSPITALET-4 | 143.75 | 0.00    | 431.25    |
| 181500 | fra.reg.prop. L´HOSPITALET-4 | 116.05 | 0.00    | 232.10    |
| 181501 | fra.reg.prop. L´HOSPITALET-4 | 416.55 | 0.00    | 416.55    |
+--------+-------------------------------+--------+--------- 
+-----------+
10 rows in set (0.11 sec)

GlobalGest=#

now it's much faster !!!!!!!!

I'm guessing what's doing as the value_sum is the same as the deure  
in some columns ...

thanks again!


raimon


On 16/05/2007, at 16:06, Richard Broersma Jr wrote:

>
> --- Raimon Fernandez <coder(at)montx(dot)com> wrote:
>> 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 ...
>>
>
> Do worry,  we are gett closer to a working query.  Each round trip  
> is providing more details that
> will help us to craft a better 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
>        AND A1.numero = A2.numero  --this should only preform a sum  
> on a specify numero
>      WHERE A1.numero=11189
>   GROUP BY A1.oid, A1.concepte, A1.deure, A1.haver
>   ORDER BY A1.oid;
>
>
>
> ---------------------------(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
>



In response to

Responses

pgsql-novice by date

Next:From: Richard Broersma JrDate: 2007-05-16 15:02:11
Subject: Re: aggregate function ?
Previous:From: Richard Broersma JrDate: 2007-05-16 14:06:49
Subject: Re: aggregate function ?

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