Re: aggregate function ? - Mailing list pgsql-novice

hi Richard,


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

>  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.numero=11189
>   GROUP BY A1.oid, A1.concepte, A1.deure, A1.haver
>   ORDER BY A1.oid;


GlobalGest=#  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.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    | 0.00      |
| 180109 | fra.reg.prop. L´HOSPITALET-4 | 121.54 | 0.00    | -381.19   |
| 180110 | fra.reg.prop. L´HOSPITALET-4 | 146.82 | 0.00    | -502.73   |
| 180111 | fra.reg.prop. L´HOSPITALET-4 | 0.00   | 1746.83 | -649.55   |
| 181496 | fra.reg.prop. L´HOSPITALET-4 | 140.46 | 0.00    | 1097.28   |
| 181497 | fra.reg.prop. L´HOSPITALET-4 | 146.36 | 0.00    | 956.82    |
| 181498 | fra.reg.prop. L´HOSPITALET-4 | 134.11 | 0.00    | 810.46    |
| 181499 | fra.reg.prop. L´HOSPITALET-4 | 143.75 | 0.00    | 676.35    |
| 181500 | fra.reg.prop. L´HOSPITALET-4 | 116.05 | 0.00    | 532.60    |
| 181501 | fra.reg.prop. L´HOSPITALET-4 | 416.55 | 0.00    | 416.55    |
+--------+-------------------------------+--------+---------
+-----------+
10 rows in set (0.01 sec)

GlobalGest=#


We  (you) are very near !!!!!

The first value_sum must be the same as the same row (deure-haver) =>
381.19

The operation is always deure-haver, I don't know why here appears in
minus (-) ...

The last row, is the same the deure as the value_sum, but it's real
data in it's real operation, both sums in that case are equal, so
it's ok.

GlobalGest=# SELECT sum(deure),sum(haver) FROM assentaments WHERE
numero=11189;
+---------+---------+
| sum     | sum(2)  |
+---------+---------+
| 1746.83 | 1746.83 |
+---------+---------+
1 rows in set (0.01 sec)

GlobalGest=#

thanks for your time !

rai