From: | Raimon Fernandez <coder(at)montx(dot)com> |
---|---|
To: | pgsql-novice(at)postgresql(dot)org |
Subject: | Re: aggregate function ? |
Date: | 2007-05-16 15:27:19 |
Message-ID: | 139750FF-9F4E-4A8E-A48E-23626E2816A5@montx.com |
Views: | Raw Message | Whole Thread | Download mbox | Resend email |
Thread: | |
Lists: | 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
From | Date | Subject | |
---|---|---|---|
Next Message | Richard Broersma Jr | 2007-05-16 15:45:22 | Re: aggregate function ? |
Previous Message | Richard Broersma Jr | 2007-05-16 15:10:06 | Re: aggregate function ? |