Re: two sums in one query

On Fri, Jul 08, 2005 at 15:49:20 +0200,
PFC <lists(at)boutiquenumerique(dot)com> wrote:
>
>
> >> 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;

If most of the records are credits or debits you don't want to do this.
A single sequential scan through the table will be the best plan.