From middink@indo.net.id Wed Oct  15 19:49:22 2003
X-Original-To: pgsql-sql-postgresql.org@localhost.postgresql.org
Received: from localhost (unknown [200.46.204.2])
	by svr1.postgresql.org (Postfix) with ESMTP id B1F82D1B4ED
	for <pgsql-sql-postgresql.org@localhost.postgresql.org>;
	Wed, 15 Oct 2003 19:49:22 +0000 (GMT)
Received: from svr1.postgresql.org ([200.46.204.71])
	by localhost (neptune.hub.org [200.46.204.2]) (amavisd-new, port 10024)
	with ESMTP id 20800-06
	for <pgsql-sql-postgresql.org@localhost.postgresql.org>;
	Wed, 15 Oct 2003 16:48:53 -0300 (ADT)
Received: from smtp-1.indo.net.id (smtp-1.indo.net.id [202.159.32.91])
	by svr1.postgresql.org (Postfix) with SMTP id F01BFD1B510
	for <pgsql-sql@postgresql.org>; Wed, 15 Oct 2003 16:48:47 -0300 (ADT)
Received: (qmail 20751 invoked from network); 16 Oct 2003 02:48:40 +0700
Received: from smtp.indo.net.id (202.159.32.81)
	by smtp-1.indo.net.id with SMTP; 16 Oct 2003 02:48:40 +0700
Received: (qmail 19856 invoked from network); 15 Oct 2003 19:46:15 -0000
Received: from unknown (HELO pop.makassar.indo.net.id) (202.159.23.19)
	by smtp.indo.net.id with SMTP; 15 Oct 2003 19:46:15 -0000
Received: FROM middinkcomp BY pop.makassar.indo.net.id ;
	Thu Oct 16 03:57:33 2003 +0700
Message-ID: <002e01c39356$d5885ca0$1f00a8c0@middinkcomp>
From: "Muhyiddin A.M Hayat" <middink@indo.net.id>
To: <pgsql-sql@postgresql.org>
Subject: Calc
Date: Thu, 16 Oct 2003 03:59:21 +0800
MIME-Version: 1.0
Content-Type: multipart/alternative;
	boundary="----=_NextPart_000_002B_01C39399.E14BB3A0"
X-Priority: 3
X-MSMail-Priority: Normal
X-Mailer: Microsoft Outlook Express 6.00.2800.1158
X-MimeOLE: Produced By Microsoft MimeOLE V6.00.2800.1165
X-Virus-Scanned: by amavisd-new at postgresql.org
X-Spam-Status: No, hits=1.2 tagged_above=0.0 required=5.0 tests=BAYES_60,
	HTML_60_70
X-Spam-Level: *
X-Archive-Number: 200310/179
X-Sequence-Number: 15579

This is a multi-part message in MIME format.

------=_NextPart_000_002B_01C39399.E14BB3A0
Content-Type: text/plain;
	charset="iso-8859-1"
Content-Transfer-Encoding: quoted-printable

Dear all,

I have below table

+-----+--------------------+---------------------+
 | id   |    db                    |cr                          |
+-----+--------------------+---------------------+
 |1     |                            | 200                      |
 |2     | 100                    |                              |
 |3     | 100                    |                              |
 |4     | 150                    |                              |
 |5     |                            | 200                      |

I Would like to calc that balance, and look like

+-----+--------------------+---------------------+-----------+
 | id   |    db                    |cr                          | bal      =
  |
+-----+--------------------+---------------------+-----------+
 |1     |                            | 200                      |200       |
 |2     | 100                    |                              |100       |
 |3     | 100                    |                              |0         =
   |
 |4     | 150                    |                              |-150      |
 |5     |                            | 200                      |50        =
 |


What can I do to get result like that










------=_NextPart_000_002B_01C39399.E14BB3A0
Content-Type: text/html;
	charset="iso-8859-1"
Content-Transfer-Encoding: quoted-printable

<!DOCTYPE HTML PUBLIC "-//W3C//DTD HTML 4.0 Transitional//EN">
<HTML><HEAD>
<META http-equiv=3DContent-Type content=3D"text/html; charset=3Diso-8859-1">
<META content=3D"MSHTML 6.00.2800.1170" name=3DGENERATOR>
<STYLE></STYLE>
</HEAD>
<BODY bgColor=3D#ffffff>
<DIV><FONT face=3DArial size=3D2>Dear all,</FONT></DIV>
<DIV><FONT face=3DArial size=3D2></FONT>&nbsp;</DIV>
<DIV><FONT face=3DArial size=3D2>I have below table</FONT></DIV>
<DIV><FONT face=3DArial size=3D2></FONT>&nbsp;</DIV>
<DIV><FONT face=3DArial=20
size=3D2>+-----+--------------------+---------------------+</FONT></DIV>
<DIV><FONT face=3DArial size=3D2>&nbsp;| id&nbsp;&nbsp;&nbsp;|&nbsp;&nbsp;&=
nbsp;=20
db&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&=
nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;=20
|cr&nbsp;&nbsp;&nbsp; &nbsp;&nbsp;&nbsp; &nbsp;&nbsp;&nbsp; &nbsp;&nbsp;&nb=
sp;=20
&nbsp;&nbsp;&nbsp; &nbsp;&nbsp;&nbsp; &nbsp;&nbsp;|</FONT></DIV>
<DIV><FONT face=3DArial=20
size=3D2>+-----+--------------------+---------------------+</FONT></DIV>
<DIV><FONT face=3DArial size=3D2>&nbsp;|1&nbsp;&nbsp;&nbsp;&nbsp;=20
|&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;=20
&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nb=
sp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;=20
|&nbsp;200&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp=
;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;=20
|</FONT></DIV>
<DIV>
<DIV><FONT face=3DArial size=3D2>&nbsp;|2&nbsp;&nbsp;&nbsp;&nbsp;=20
|&nbsp;100&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp=
;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;=20
|&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&n=
bsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp=
;&nbsp;&nbsp;&nbsp;&nbsp;=20
|</FONT></DIV>
<DIV>
<DIV><FONT face=3DArial size=3D2>&nbsp;|3&nbsp;&nbsp;&nbsp;&nbsp;=20
|&nbsp;100&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp=
;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;=20
|&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&n=
bsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp=
;&nbsp;&nbsp;&nbsp;&nbsp;=20
|</FONT></DIV>
<DIV>
<DIV><FONT face=3DArial size=3D2>&nbsp;|4&nbsp;&nbsp;&nbsp;&nbsp;=20
|&nbsp;150&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp=
;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;=20
|&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&n=
bsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp=
;&nbsp;&nbsp;&nbsp;&nbsp;=20
|</FONT></DIV>
<DIV>
<DIV><FONT face=3DArial size=3D2>&nbsp;|5&nbsp;&nbsp;&nbsp;&nbsp;=20
|&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;=20
&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nb=
sp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;=20
|&nbsp;200&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp=
;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;=20
|</FONT></DIV>
<DIV><FONT face=3DArial size=3D2></FONT>&nbsp;</DIV>
<DIV><FONT face=3DArial size=3D2>I Would like to calc that balance, and loo=
k=20
like</FONT></DIV>
<DIV><FONT face=3DArial size=3D2></FONT>&nbsp;</DIV>
<DIV>
<DIV><FONT face=3DArial=20
size=3D2>+-----+--------------------+---------------------+-----------+</FO=
NT></DIV>
<DIV><FONT face=3DArial size=3D2>&nbsp;| id&nbsp;&nbsp;&nbsp;|&nbsp;&nbsp;&=
nbsp;=20
db&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&=
nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;=20
|cr&nbsp;&nbsp;&nbsp; &nbsp;&nbsp;&nbsp; &nbsp;&nbsp;&nbsp; &nbsp;&nbsp;&nb=
sp;=20
&nbsp;&nbsp;&nbsp; &nbsp;&nbsp;&nbsp; &nbsp;&nbsp;|=20
bal&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp; |</FONT></DIV>
<DIV><FONT face=3DArial=20
size=3D2>+-----+--------------------+---------------------+-----------+</FO=
NT></DIV>
<DIV><FONT face=3DArial size=3D2>&nbsp;|1&nbsp;&nbsp;&nbsp;&nbsp;=20
|&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;=20
&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nb=
sp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;=20
|&nbsp;200&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp=
;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;=20
|200&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp; |</FONT></DIV>
<DIV>
<DIV><FONT face=3DArial size=3D2>&nbsp;|2&nbsp;&nbsp;&nbsp;&nbsp;=20
|&nbsp;100&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp=
;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;=20
|&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&n=
bsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp=
;&nbsp;&nbsp;&nbsp;&nbsp;=20
|100&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp; |</FONT></DIV>
<DIV>
<DIV><FONT face=3DArial size=3D2>&nbsp;|3&nbsp;&nbsp;&nbsp;&nbsp;=20
|&nbsp;100&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp=
;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;=20
|&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&n=
bsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp=
;&nbsp;&nbsp;&nbsp;&nbsp;=20
|0&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;=20
|</FONT></DIV>
<DIV>
<DIV><FONT face=3DArial size=3D2>&nbsp;|4 &nbsp;&nbsp;&nbsp;=20
|&nbsp;150&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp=
;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;=20
|&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&n=
bsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp=
;&nbsp;&nbsp;&nbsp;&nbsp;=20
|-150&nbsp;&nbsp;&nbsp;&nbsp;&nbsp; |</FONT></DIV>
<DIV>
<DIV><FONT face=3DArial size=3D2>&nbsp;|5&nbsp;&nbsp;&nbsp;&nbsp;=20
|&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;=20
&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nb=
sp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;=20
|&nbsp;200&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp=
;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;=20
|50&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp; |</FONT></DIV>
<DIV><FONT face=3DArial size=3D2></FONT>&nbsp;</DIV>
<DIV><FONT face=3DArial size=3D2></FONT>&nbsp;</DIV>
<DIV><FONT face=3DArial size=3D2>What can I do to get result like that</FON=
T></DIV>
<DIV><FONT face=3DArial size=3D2></FONT>&nbsp;</DIV>
<DIV><FONT face=3DArial size=3D2></FONT>&nbsp;</DIV>
<DIV><FONT face=3DArial size=3D2></FONT>&nbsp;</DIV>
<DIV><FONT face=3DArial size=3D2></FONT>&nbsp;</DIV></DIV></DIV></DIV></DIV=
></DIV>
<DIV><FONT face=3DArial size=3D2></FONT>&nbsp;</DIV>
<DIV><FONT face=3DArial size=3D2></FONT>&nbsp;</DIV>
<DIV><FONT face=3DArial size=3D2></FONT>&nbsp;</DIV>
<DIV><FONT face=3DArial size=3D2></FONT>&nbsp;</DIV>
<DIV><FONT face=3DArial size=3D2></FONT>&nbsp;</DIV>
<DIV><FONT face=3DArial=20
size=3D2></FONT>&nbsp;</DIV></DIV></DIV></DIV></DIV></BODY></HTML>

------=_NextPart_000_002B_01C39399.E14BB3A0--





From scrawford@pinpointresearch.com Wed Oct  15 20:31:58 2003
X-Original-To: pgsql-sql-postgresql.org@localhost.postgresql.org
Received: from localhost (unknown [200.46.204.2])
	by svr1.postgresql.org (Postfix) with ESMTP id 383FAD1B53E
	for <pgsql-sql-postgresql.org@localhost.postgresql.org>;
	Wed, 15 Oct 2003 20:31:58 +0000 (GMT)
Received: from svr1.postgresql.org ([200.46.204.71])
	by localhost (neptune.hub.org [200.46.204.2]) (amavisd-new, port 10024)
	with ESMTP id 23399-08
	for <pgsql-sql-postgresql.org@localhost.postgresql.org>;
	Wed, 15 Oct 2003 17:31:29 -0300 (ADT)
Received: from polaris.pinpointresearch.com (66-7-238-130.cust.telepacific.net
	[66.7.238.130])
	by svr1.postgresql.org (Postfix) with ESMTP id 10686D1B4F2
	for <pgsql-sql@postgresql.org>; Wed, 15 Oct 2003 17:31:26 -0300 (ADT)
Received: from betelgeuse.pinpointresearch.com
	(betelgeuse.pinpointresearch.com [192.168.1.179])
	by polaris.pinpointresearch.com (Postfix) with ESMTP
	id 83AFA103C7; Wed, 15 Oct 2003 13:31:22 -0700 (PDT)
From: Steve Crawford <scrawford@pinpointresearch.com>
Organization: Pinpoint Research
To: "Muhyiddin A.M Hayat" <middink@indo.net.id>,
	<pgsql-sql@postgresql.org>
Subject: Re: Calc
Date: Wed, 15 Oct 2003 13:31:22 -0700
User-Agent: KMail/1.5.1
References: <002e01c39356$d5885ca0$1f00a8c0@middinkcomp>
In-Reply-To: <002e01c39356$d5885ca0$1f00a8c0@middinkcomp>
MIME-Version: 1.0
Content-Type: text/plain;
  charset="iso-8859-1"
Content-Transfer-Encoding: 7bit
Content-Disposition: inline
Message-Id: <200310151331.22516.scrawford@pinpointresearch.com>
X-Virus-Scanned: by amavisd-new at postgresql.org
X-Archive-Number: 200310/180
X-Sequence-Number: 15580

On Wednesday 15 October 2003 12:59 pm, Muhyiddin A.M Hayat wrote:
> Dear all,
>
> I have below table
>
> +-----+--------------------+---------------------+
>
>  | id   |    db                    |cr                          |
>
> +-----+--------------------+---------------------+
>
>  |1     |                            | 200                      |
>  |2     | 100                    |                              |
>  |3     | 100                    |                              |
>  |4     | 150                    |                              |
>  |5     |                            | 200                      |
>
> I Would like to calc that balance, and look like
>
> +-----+--------------------+---------------------+-----------+
>
>  | id   |    db                    |cr                          |
>  | bal        |
>
> +-----+--------------------+---------------------+-----------+
>
>  |1     |                            | 200                     
>  | |200       | 2     | 100                    |                   
>  |           |100       | 3     | 100                    |         
>  |                     |0            | 4     | 150                 
>  |   |                              |-150      | 5     |           
>  |                 | 200                      |50         |
>
> What can I do to get result like that

Something like this:

select id,db,cr,(select sum(cr-db) from calc sub where sub.id <= 
calc.id) from calc;

This of course assumes that ID indicates the correct order of the 
entries and it will blow up if you allow nulls for cr or db (which 
you shouldn't since that would literally be interpreted as "they 
withdrew 10 and deposited an unknown amount"). If you have null 
values already and they should be interpreted as 0 just do this:

select id, db, cr, (select sum(coalesce(cr,0)-coalesce(db,0)) from 
calc sub where sub.id <= calc.id) from calc;

I assume no responsibility for potential lack of scalability of this 
query. :) It's quite possible a faster solution exists - we'll see 
what comes up on the list.

Cheers,
Steve




From josh@agliodbs.com Wed Oct  15 22:18:23 2003
X-Original-To: pgsql-sql-postgresql.org@localhost.postgresql.org
Received: from localhost (unknown [200.46.204.2])
	by svr1.postgresql.org (Postfix) with ESMTP id 18C8FD1B502
	for <pgsql-sql-postgresql.org@localhost.postgresql.org>;
	Wed, 15 Oct 2003 22:18:23 +0000 (GMT)
Received: from svr1.postgresql.org ([200.46.204.71])
	by localhost (neptune.hub.org [200.46.204.2]) (amavisd-new, port 10024)
	with ESMTP id 50582-01
	for <pgsql-sql-postgresql.org@localhost.postgresql.org>;
	Wed, 15 Oct 2003 19:17:54 -0300 (ADT)
Received: from davinci.ethosmedia.com (unknown [209.128.84.228])
	by svr1.postgresql.org (Postfix) with ESMTP id 3D8C8D1B510
	for <pgsql-sql@postgresql.org>; Wed, 15 Oct 2003 19:17:51 -0300 (ADT)
Received: from [66.219.92.2] (HELO temoku)
	by davinci.ethosmedia.com (CommuniGate Pro SMTP 4.0.2)
	with ESMTP id 3773191; Wed, 15 Oct 2003 15:18:32 -0700
Content-Type: text/plain;
  charset="iso-8859-1"
From: Josh Berkus <josh@agliodbs.com>
Reply-To: josh@agliodbs.com
Organization: Aglio Database Solutions
To: "Muhyiddin A.M Hayat" <middink@indo.net.id>,
	<pgsql-sql@postgresql.org>
Subject: Re: Calc
Date: Wed, 15 Oct 2003 15:15:43 -0700
User-Agent: KMail/1.4.3
References: <002e01c39356$d5885ca0$1f00a8c0@middinkcomp>
In-Reply-To: <002e01c39356$d5885ca0$1f00a8c0@middinkcomp>
MIME-Version: 1.0
Content-Transfer-Encoding: quoted-printable
Message-Id: <200310151515.43318.josh@agliodbs.com>
X-Virus-Scanned: by amavisd-new at postgresql.org
X-Archive-Number: 200310/182
X-Sequence-Number: 15582


There have been several discussions on running totals on this list over the=
=20
last couple of weeks.  See the archives for possible solutions.

--=20
-Josh Berkus
 Aglio Database Solutions
 San Francisco




From scrawford@pinpointresearch.com Thu Oct  16 17:36:12 2003
X-Original-To: pgsql-sql-postgresql.org@localhost.postgresql.org
Received: from localhost (unknown [200.46.204.2])
	by svr1.postgresql.org (Postfix) with ESMTP id 5C617D1B557
	for <pgsql-sql-postgresql.org@localhost.postgresql.org>;
	Thu, 16 Oct 2003 17:36:12 +0000 (GMT)
Received: from svr1.postgresql.org ([200.46.204.71])
	by localhost (neptune.hub.org [200.46.204.2]) (amavisd-new, port 10024)
	with ESMTP id 51301-07
	for <pgsql-sql-postgresql.org@localhost.postgresql.org>;
	Thu, 16 Oct 2003 14:35:42 -0300 (ADT)
Received: from polaris.pinpointresearch.com (66-7-238-130.cust.telepacific.net
	[66.7.238.130])
	by svr1.postgresql.org (Postfix) with ESMTP id B055FD1B508
	for <pgsql-sql@postgresql.org>; Thu, 16 Oct 2003 14:35:40 -0300 (ADT)
Received: from betelgeuse.pinpointresearch.com
	(betelgeuse.pinpointresearch.com [192.168.1.179])
	by polaris.pinpointresearch.com (Postfix) with ESMTP
	id C3566FE0A; Thu, 16 Oct 2003 10:35:35 -0700 (PDT)
From: Steve Crawford <scrawford@pinpointresearch.com>
Organization: Pinpoint Research
To: "Muhyiddin A.M Hayat" <middink@indo.net.id>
Subject: Re: Calc
Date: Thu, 16 Oct 2003 10:35:35 -0700
User-Agent: KMail/1.5.1
Cc: <pgsql-sql@postgresql.org>
References: <002e01c39356$d5885ca0$1f00a8c0@middinkcomp>
	<200310151331.22516.scrawford@pinpointresearch.com>
	<001101c3940c$29f004e0$1f00a8c0@middinkcomp>
In-Reply-To: <001101c3940c$29f004e0$1f00a8c0@middinkcomp>
MIME-Version: 1.0
Content-Type: text/plain;
  charset="iso-8859-1"
Content-Transfer-Encoding: 7bit
Content-Disposition: inline
Message-Id: <200310161035.35762.scrawford@pinpointresearch.com>
X-Virus-Scanned: by amavisd-new at postgresql.org
X-Archive-Number: 200310/194
X-Sequence-Number: 15594

On Thursday 16 October 2003 10:37 am, Muhyiddin A.M Hayat wrote:
> > Something like this:
> >
> > select id,db,cr,(select sum(cr-db) from calc sub where sub.id <=
> > calc.id) from calc;
> >
> > This of course assumes that ID indicates the correct order of the
> > entries and it will blow up if you allow nulls for cr or db
> > (which you shouldn't since that would literally be interpreted as
> > "they withdrew 10 and deposited an unknown amount"). If you have
> > null values already and they should be interpreted as 0 just do
> > this:
> >
> > select id, db, cr, (select sum(coalesce(cr,0)-coalesce(db,0))
> > from calc sub where sub.id <= calc.id) from calc;
> >
> > I assume no responsibility for potential lack of scalability of
> > this query. :) It's quite possible a faster solution exists -
> > we'll see what comes up on the list.
>
> If  data from "View" without ID, how can I do?
>
> My View:
>
>   trx_date  | trx_time |                      descriptions    |
> payment_method | debet |  credit  | creator
> ------------+----------+--------------------------------------+----
>--------- ---+-------+----------+---------
>  2003-10-09 | 21:55:02 | Resto Biling : 13,800, Paid : 10,000 |
> Visa
>
> |  3800 |          | middink
>
>  2003-10-16 | 03:28:30 | Payment - Thank You                  |
> Visa
>
> |       | 40000.00 | middink
>
>  2003-10-08 | 18:17:40 | Payment - Thank You                  |
> Cash
>
> |       | 50000.00 

The id field only keeps the transactions in the correct order so you 
can sum the previous transactions. You can do the same thing with 
your date and time fields (though using a single datetime field would 
be easier). A caution, though: you will have trouble if two 
transactions share the same date and time.

Cheers,
Steve





From middink@indo.net.id Thu Oct  16 17:27:40 2003
X-Original-To: pgsql-sql-postgresql.org@localhost.postgresql.org
Received: from localhost (unknown [200.46.204.2])
	by svr1.postgresql.org (Postfix) with ESMTP id 7499ED1B4ED
	for <pgsql-sql-postgresql.org@localhost.postgresql.org>;
	Thu, 16 Oct 2003 17:27:40 +0000 (GMT)
Received: from svr1.postgresql.org ([200.46.204.71])
	by localhost (neptune.hub.org [200.46.204.2]) (amavisd-new, port 10024)
	with ESMTP id 46275-06
	for <pgsql-sql-postgresql.org@localhost.postgresql.org>;
	Thu, 16 Oct 2003 14:27:10 -0300 (ADT)
Received: from smtp-1.indo.net.id (smtp-1.indo.net.id [202.159.32.91])
	by svr1.postgresql.org (Postfix) with SMTP id C312DD1B508
	for <pgsql-sql@postgresql.org>; Thu, 16 Oct 2003 14:27:06 -0300 (ADT)
Received: (qmail 30401 invoked from network); 17 Oct 2003 00:26:59 +0700
Received: from smtp.indo.net.id (202.159.32.81)
	by smtp-1.indo.net.id with SMTP; 17 Oct 2003 00:26:59 +0700
Received: (qmail 12089 invoked from network); 16 Oct 2003 17:24:34 -0000
Received: from unknown (HELO pop.makassar.indo.net.id) (202.159.23.19)
	by smtp.indo.net.id with SMTP; 16 Oct 2003 17:24:34 -0000
Received: FROM middinkcomp BY pop.makassar.indo.net.id ;
	Fri Oct 17 01:35:32 2003 +0700
Message-ID: <001101c3940c$29f004e0$1f00a8c0@middinkcomp>
From: "Muhyiddin A.M Hayat" <middink@indo.net.id>
To: "Steve Crawford" <scrawford@pinpointresearch.com>
Cc: <pgsql-sql@postgresql.org>
References: <002e01c39356$d5885ca0$1f00a8c0@middinkcomp>
	<200310151331.22516.scrawford@pinpointresearch.com>
Subject: Re: Calc
Date: Fri, 17 Oct 2003 01:37:08 +0800
MIME-Version: 1.0
Content-Type: text/plain;
	charset="iso-8859-1"
Content-Transfer-Encoding: 7bit
X-Priority: 3
X-MSMail-Priority: Normal
X-Mailer: Microsoft Outlook Express 6.00.2800.1158
X-MimeOLE: Produced By Microsoft MimeOLE V6.00.2800.1165
X-Virus-Scanned: by amavisd-new at postgresql.org
X-Archive-Number: 200310/193
X-Sequence-Number: 15593

> Something like this:
>
> select id,db,cr,(select sum(cr-db) from calc sub where sub.id <=
> calc.id) from calc;
>
> This of course assumes that ID indicates the correct order of the
> entries and it will blow up if you allow nulls for cr or db (which
> you shouldn't since that would literally be interpreted as "they
> withdrew 10 and deposited an unknown amount"). If you have null
> values already and they should be interpreted as 0 just do this:
>
> select id, db, cr, (select sum(coalesce(cr,0)-coalesce(db,0)) from
> calc sub where sub.id <= calc.id) from calc;
>
> I assume no responsibility for potential lack of scalability of this
> query. :) It's quite possible a faster solution exists - we'll see
> what comes up on the list.
>


If  data from "View" without ID, how can I do?

My View:

  trx_date  | trx_time |                      descriptions    |
payment_method | debet |  credit  | creator
------------+----------+--------------------------------------+-------------
---+-------+----------+---------
 2003-10-09 | 21:55:02 | Resto Biling : 13,800, Paid : 10,000 | Visa
|  3800 |          | middink
 2003-10-16 | 03:28:30 | Payment - Thank You                  | Visa
|       | 40000.00 | middink
 2003-10-08 | 18:17:40 | Payment - Thank You                  | Cash
|       | 50000.00 | middink







From middink@indo.net.id Fri Oct  17 04:57:04 2003
X-Original-To: pgsql-sql-postgresql.org@localhost.postgresql.org
Received: from localhost (unknown [200.46.204.2])
	by svr1.postgresql.org (Postfix) with ESMTP id CF494D1B4E3
	for <pgsql-sql-postgresql.org@localhost.postgresql.org>;
	Fri, 17 Oct 2003 04:57:04 +0000 (GMT)
Received: from svr1.postgresql.org ([200.46.204.71])
	by localhost (neptune.hub.org [200.46.204.2]) (amavisd-new, port 10024)
	with ESMTP id 63322-07
	for <pgsql-sql-postgresql.org@localhost.postgresql.org>;
	Fri, 17 Oct 2003 01:56:34 -0300 (ADT)
Received: from smtp-1.indo.net.id (smtp-1.indo.net.id [202.159.32.91])
	by svr1.postgresql.org (Postfix) with SMTP id 38A8ED1B4E6
	for <pgsql-sql@postgresql.org>; Fri, 17 Oct 2003 01:56:30 -0300 (ADT)
Received: (qmail 8009 invoked from network); 17 Oct 2003 11:56:24 +0700
Received: from smtp.indo.net.id (202.159.32.81)
	by smtp-1.indo.net.id with SMTP; 17 Oct 2003 11:56:24 +0700
Received: (qmail 7848 invoked from network); 17 Oct 2003 04:53:59 -0000
Received: from unknown (HELO pop.makassar.indo.net.id) (202.159.23.19)
	by smtp.indo.net.id with SMTP; 17 Oct 2003 04:53:59 -0000
Received: FROM middinkcomp BY pop.makassar.indo.net.id ;
	Fri Oct 17 13:05:20 2003 +0700
Message-ID: <000001c3946c$86ff1a30$1f00a8c0@middinkcomp>
From: "Muhyiddin A.M Hayat" <middink@indo.net.id>
To: "Steve Crawford" <scrawford@pinpointresearch.com>
Cc: <pgsql-sql@postgresql.org>
References: <002e01c39356$d5885ca0$1f00a8c0@middinkcomp>
	<200310151331.22516.scrawford@pinpointresearch.com>
	<001101c3940c$29f004e0$1f00a8c0@middinkcomp>
	<200310161035.35762.scrawford@pinpointresearch.com>
Subject: Re: Calc
Date: Fri, 17 Oct 2003 09:38:05 +0800
MIME-Version: 1.0
Content-Type: text/plain;
	charset="iso-8859-1"
Content-Transfer-Encoding: 7bit
X-Priority: 3
X-MSMail-Priority: Normal
X-Mailer: Microsoft Outlook Express 6.00.2800.1158
X-MimeOLE: Produced By Microsoft MimeOLE V6.00.2800.1165
X-Virus-Scanned: by amavisd-new at postgresql.org
X-Archive-Number: 200310/201
X-Sequence-Number: 15601


> >   trx_date  | trx_time |                      descriptions    |
> > payment_method | debet |  credit  | creator
> > ------------+----------+--------------------------------------+----
> >--------- ---+-------+----------+---------
> >  2003-10-09 | 21:55:02 | Resto Biling : 13,800, Paid : 10,000 |
> > Visa
> >
> > |  3800 |          | middink
> >
> >  2003-10-16 | 03:28:30 | Payment - Thank You                  |
> > Visa
> >
> > |       | 40000.00 | middink
> >
> >  2003-10-08 | 18:17:40 | Payment - Thank You                  |
> > Cash
> >
> > |       | 50000.00
>
> The id field only keeps the transactions in the correct order so you
> can sum the previous transactions. You can do the same thing with
> your date and time fields (though using a single datetime field would
> be easier). A caution, though: you will have trouble if two
> transactions share the same date and time.

if i would like to pleaced ID(Virtual ID)  for example number of record for
each record, how to ?


 num_rec |  trx_date  | trx_time |  credit  |
descriptions                      | payment_method | debet
---------+------------+----------+----------+-------------------------------
-------------------------+----------------+-------
       1 | 2003-10-09 | 21:55:02 |          | Resto Biling : Rp.
13,800, Paid : Rp.      10,000 | Visa           |  3800
       2 | 2003-10-16 | 03:28:30 | 40000.00 | Payment
| Visa           |
       3 | 2003-10-08 | 18:17:40 | 50000.00 | Payment
| Cash           |