>> 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