Re: two sums in one query

On Fri, Jul 08, 2005 at 15:49:20 +0200, PFC <lists@boutiquenumerique.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.