rakesh17684
New User
Joined: 08 Oct 2006
Posts: 61
Location: San Diego
|
|
|
|
Hi ,
I have 2 tables from which i need to get the counts of debits,credits for a list of account numbers. These 2 tables are related by a column called deposit number.
table a
| Code: |
type accountno deposit no.
cr 000110 1234
db 000321 1234
db 31200 1234
cr 000220 5678
db 789000 5678
cr 23452 4324
db 48623 4324 |
table b
| Code: |
type accountno deposit no.
db1 5435345 1234
db1 89076 1234
db1 789789 1234
db1 9875576 5678
db1 534534534 5678 |
when my inputs to the query is 000110 ,000220 the output should be
| Code: |
account no type count
000110 cr 1
000110 db 5
000220 cr 1
000220 db 3 |
P.s the debits have a value of db in table a and value of db1 in table b
tried my best, so far no luck 
Thanks in advance for any tips or hints
Rakesh |
|
GuyC
Senior Member
Joined: 11 Aug 2009
Posts: 1281
Location: Belgium
|
|
|
|
this works for cr=> db, I don't know what you want as output when you enter a db account like 789789 as input.
| Code: |
select type,accountno,count(*)
from
(select accountno,'cr' as type
from tabA where type = 'cr'
union all
select A.accountno,'db' as type
from tabA a1 , taba a2
where a2.depositno = a1.depositno
and a1.type = 'cr' and a2.type = 'db'
union all
select A.accountno,'db' as type
from tabA a1 , tabb b2
where b2.depositno = a1.depositno
and a1.type = 'cr' and b2.type = 'db1'
) X
group by type,accountno |
|
|
