Thread: two sums in one query
hi i have a table like this: serialno varchar, debit integer, credit integer, amount numeric the columns 'debit' and 'credit' refer to the acount number. I want to query the table to select all the rows where either debit = account or credit=account and to get two sums - one of the amounts where the account is on the debit side and the other on the credit side. Can i do this in one query? if so, how? -- regards kg http://www.livejournal.com/users/lawgon tally ho! http://avsap.org.in ಇಂಡ್ಲಿನಕ್ಸ வாழ்க!
On Friday 08 Jul 2005 12:02 pm, Ramakrishnan Muralidharan wrote: > I have assuemed that the row will having eighter Debit account > or Credit account, the following Query will give sum of debit and > credit accounts > > 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> 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 -- regards kg http://www.livejournal.com/users/lawgon tally ho! http://avsap.org.in ಇಂಡ್ಲಿನಕ್ಸ வாழ்க!
>> 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
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.
>> 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. I thought that debit = source account # and credit = dest account #, and there are a lot of different account...
On Fri, Jul 08, 2005 at 16:49:44 +0200, PFC <lists@boutiquenumerique.com> wrote: > > >>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. > > I thought that debit = source account # and credit = dest account #, > and there are a lot of different account... You aren't likely to see a significant speed up in that case either.
I have assuemed that the row will having eighter Debit account or Credit account, the following Query will give sum
ofdebit and credit accounts
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>
Regards,
R.Muralidharan
-----Original Message-----
From: pgsql-sql-owner@postgresql.org
[mailto:pgsql-sql-owner@postgresql.org]On Behalf Of Kenneth Gonsalves
Sent: Friday, July 08, 2005 11:18 AM
To: pgsql-sql@postgresql.org
Subject: [SQL] two sums in one query
hi
i have a table like this:
serialno varchar,
debit integer,
credit integer,
amount numeric
the columns 'debit' and 'credit' refer to the acount number. I want to
query the table to select all the rows where either debit = account
or credit=account and to get two sums - one of the amounts where the
account is on the debit side and the other on the credit side. Can i
do this in one query? if so, how?
--
regards
kg
http://www.livejournal.com/users/lawgon
tally ho! http://avsap.org.in
ಇಂಡ್ಲಿನಕ್ಸ வாழ்க!
---------------------------(end of broadcast)---------------------------
TIP 6: explain analyze is your friend