Selecting count(*) from multiple tables

Database
Enthusiast

Selecting count(*) from multiple tables

Hi,

I have 3 tables say

a) account_details  b) credit_trans    c) debit_trans

I want a query which results in the total number of credit transaction and total number of debit transaction for each account

Table 1

create table account_details

(

acc_no integer,

sort_code integer,

name varchar(20)

)

unique primary index(acc_no,sort_code);

acc_no      sort_code   name

----------- ----------- --------------------

111         112         abc

113         114         hij

115         116         pqr

117         118         xyz


Table 2

create table credit_trans

(

acc_no integer,

sort_code integer,

trans_amt float,

trans_date date default getdate()

)

primary index(acc_no,sort_code);

acc_no      sort_code   trans_amt              trans_date

----------- ----------- ---------------------- ----------

111         112         10000                  2013-06-24

111         112         10000                  2013-06-23

113         114         5000                   2013-06-24


Table 3


create table debit_trans

(

acc_no integer,

sort_code integer,

trans_amt float,

trans_date date default getdate()

)

primary index(acc_no,sort_code);


acc_no      sort_code   trans_amt              trans_date

----------- ----------- ---------------------- ----------

111         112         2000                   2013-06-24

113         114         1000                   2013-06-23

113         114         1000                   2013-06-24

Expected output

acc_no      sort_code     total_credit     total debit

111         112                     2                  1

113         114                     1                  2

115         116                     0                  0

117         118                     0                  0

Thanks in advance

4 REPLIES

Re: Selecting count(*) from multiple tables

Hi

try out the below query ... it will solve your proble

select acc_no, sort_code, count(credit.acc_no), count(debit.acc_no)

from account_details acct

inner join credit_trans credit

on acct.acc_no = credit.acc_no

inner join debit_trans debit

on acct.acc_no = debit.acc_no

group by acc_no, sort_code

Thanks

LearnDWH

Enthusiast

Re: Selecting count(*) from multiple tables

Thanks for the suggestion but there are ambiguity errors. I tried  with acct to resolve ambiguity but that wont result in the output..

Output for this query is

acc_no      sort_code               

----------- ----------- ----------- -----------

111         112         2           2

113         114         2           2

Re: Selecting count(*) from multiple tables

Yes got it..try this query

select a.acc_no, a.sort_code, sum(credit_count), sum(debit_count)

(select acct.acc_no, acct.sort_code, count(credit.acc_no) as credit_count, 0 as debit_count

from account_details acct

left outer join credit_trans credit

on acct.acc_no = credit.acc_no

union all

select acct.acc_no, acct.sort_code, 0 as credit_count, count(debit.acc_no) as debit_count

from account_details acct

left outer join debit_trans debit

on acct.acc_no = debit.acc_no

group by acc_no, sort_code) A

group by 1,2

there might be syntactial error since i have not executed. please check it if you get any

Senior Apprentice

Re: Selecting count(*) from multiple tables

You need to do the aggregates within Dervied Tables and then Outer join them:

SELECT
acct.acc_no,
acct.sort_code,
COALESCE(credit.cnt, 0) AS credit_count,
COALESCE(debit.cnt, 0) AS debit_count
FROM account_details acct
LEFT JOIN
(
SELECT acc_no, sort_code, COUNT(*) AS cnt
FROM credit_trans
) AS credit
ON acct.acc_no = credit.acc_no
AND acct.sort_code = credit.sort_code
LEFT JOIN
(
SELECT acc_no, sort_code, COUNT(*) AS cnt
FROM debit_trans
) AS debit
on acct.acc_no = debit.acc_no
AND acct.sort_code = debit.sort_code

Dieter