Re: aggregate function ?

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
>