X-Original-To: pgsql-sql-postgresql.org@localhost.postgresql.org
Received: from localhost (unknown [200.46.204.144])
	by svr1.postgresql.org (Postfix) with ESMTP id 2852A52A19
	for <pgsql-sql-postgresql.org@localhost.postgresql.org>;
	Fri,  8 Jul 2005 10:49:32 -0300 (ADT)
Received: from svr1.postgresql.org ([200.46.204.71])
	by localhost (av.hub.org [200.46.204.144]) (amavisd-new, port 10024)
	with ESMTP id 27789-02
	for <pgsql-sql-postgresql.org@localhost.postgresql.org>;
	Fri,  8 Jul 2005 13:49:22 +0000 (GMT)
Received: from boutiquenumerique.com (boutiquenumerique.com [82.67.9.10])
	by svr1.postgresql.org (Postfix) with ESMTP id B8EF252A20
	for <pgsql-sql@postgresql.org>; Fri,  8 Jul 2005 10:49:18 -0300 (ADT)
Received: (qmail 23513 invoked from network); 8 Jul 2005 15:49:49 +0200
Received: from unknown (HELO localhost) (boutiquenumerique-lists@192.168.0.4)
	by boutiquenumerique.com with SMTP; 8 Jul 2005 15:49:49 +0200
Date: Fri, 08 Jul 2005 15:49:20 +0200
To: "Kenneth Gonsalves" <lawgon@thenilgiris.com>,
	"Ramakrishnan Muralidharan" <ramakrishnanm@pervasive-postgres.com>
Subject: Re: two sums in one query
Cc: pgsql-sql@postgresql.org
References: 
 <02767D4600E59A4487233B23AEF5C5992A4093@blrmail1.aus.pervasive.com>
	<200507081306.08597.lawgon@thenilgiris.com>
From: PFC <lists@boutiquenumerique.com>
Organization: =?iso-8859-15?Q?La_Boutique_Num=E9rique?=
Content-Type: text/plain; format=flowed; delsp=yes; charset=iso-8859-15
MIME-Version: 1.0
Content-Transfer-Encoding: 8bit
Message-ID: <op.stlkoiq4th1vuj@localhost>
In-Reply-To: <200507081306.08597.lawgon@thenilgiris.com>
User-Agent: Opera M2/8.0 (Linux, build 1095)
X-Virus-Scanned: by amavisd-new at hub.org
X-Spam-Status: No, hits=0.109 tagged_above=0 required=5 tests=AWL,
	UPPERCASE_25_50
X-Spam-Level: 
X-Archive-Number: 200507/67
X-Sequence-Number: 22045



>>    SELECT  SUM( CASE WHEN COALESCE( DEBIT , 0 ) <> 0 THEN
>> COALESCE( AMOUNT , 0 ) ELSE 0 END ) AS DEBIT_AMT , SUM( CASE WHEN
>> COALESCE( CREDIT , 0 ) <> 0 THEN   COALESCE( AMOUNT , 0 ) ELSE 0
>> END ) AS CREDIT_AMT FROM <TABLE NAME>

	I don't know if it will use indexes (bitmapped OR indexes in 8.1 ?)... if  
you have indexes on debit and on credit, you could do

SELECT (SELECT sum(amount) FROM table WHERE debit=X) AS debit, (SELECT  
sum(amount) FROM table WHERE credit=x) AS credit;

>
> actually, all the rows have both 'debit' and 'credit', but based on
> the hint you gave i made this query which works:
>
> select
> sum(case when debit=account then amount else 0 end) as debtotal,
> sum(case when credit=account then amount else 0 end) as credtotal
> from voucherrows
> where
> debit = account
> or
> credit = account
>
> thanks for taking the trouble - i never believed this was possible,
> just asked on the off chance that it *may* be possible