From: PFC <lists@boutiquenumerique.com>
To: Kenneth Gonsalves <lawgon@thenilgiris.com>
To: Ramakrishnan Muralidharan <ramakrishnanm@pervasive-postgres.com>
Cc: pgsql-sql@postgresql.org
Subject: Re: two sums in one query
Date: Fri, 08 Jul 2005 15:49:20 +0200
Message-ID: <op.stlkoiq4th1vuj@localhost> (raw)
In-Reply-To: <200507081306.08597.lawgon@thenilgiris.com>
References: <02767D4600E59A4487233B23AEF5C5992A4093@blrmail1.aus.pervasive.com>
<200507081306.08597.lawgon@thenilgiris.com>
>> 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
Reply instructions:
You may reply publicly to this message via plain-text email
using any one of the following methods:
* Reply to all the recipients using the --to and --cc options:
reply via email
To: pgsql-sql@postgresql.org
Cc: lists@boutiquenumerique.com, lawgon@thenilgiris.com, ramakrishnanm@pervasive-postgres.com
Subject: Re: two sums in one query
In-Reply-To: <op.stlkoiq4th1vuj@localhost>
* Save the following mbox file, import it into your mail client,
and reply-to-all from there: mbox
This inbox is served by DDX for PostgreSQL; see mirroring instructions
for how to clone and mirror all data and code used for this inbox