Different subsets of the same data in a single report

[simonfisher]

I am trying to create a report with three columns. The first column indicates the total number of Transactions for each Type of Bank Account for a given period (acnt_type). Of this total no. of transactions, I need to display the number of ATM transactions, in the second column. The third column is to display the Mode() for the ATM Transactions, for each Type of Bank Account, ie. I need to display a count of the highest number of ATM transactions within each Account Type. I have developed the SQL queries for the first two columns, but am unable to combine them in a single report.

Query 1 Total No. of Trans

select acnt_type, int_cat, count(acnt_no) from acnt_hist
where period = 200503
and status = 0
and system = 'DEP'
group by acnt_type, int_cat

ACNT_TYPE INT_CAT COUNT(ACNT_NO)
---------- ---------- --------------
1 1 5853
1 2 739
1 4 1411
1 7 2
1 21 1
1 101 6848

Query 2 Total No. of ATM Trans

select acnt_type, int_cat, count(*) ATM
from acnt_hist a, tran_hist t
where a.acnt_no = t.acnt_no
and a.period = t.period
and system = 'DEP'
and status = 0
and a.period = 200503
and promo = 'EA'
group by acnt_type, int_cat

ACNT_TYPE INT_CAT ATM
---------- ---------- ----------
1 1 3517
1 2 476
1 4 510
1 21 1
1 101 5152
1 102 914

I have no idea how to calculate the Mode.

Any help would be greatly appreciated.

Thanx

Simon Fisher

 

[MisterMo]

You could use a Stored Proc

create two temp tables

place the data from the two onto them and then join the two temp table by ACNT_TYPE and INT_CAT.

Mo

 

[davidwendelken]

What database and version are you connecting to? There are some SQL tricks that might help.

David Wendelken

http://www.casetech.net