rhexis07
01-19-2008, 09:57 PM
Hi.
I have a table called mbs_audittrail_detail, and it has the following columns,
Audittrailno | SessionID | Amount | Trans_Code
01 | 01 | 1000 | SBMN05RQ
02 | 01 | 2000 | ACWDXXRQ
03 | 01 | 0000 | RDXXXXRQ
04 | 02 | 1500 | SBMN05RQ
05 | 02 | 1500 | ACWDXXRQ
The problem I have is, I'd like to retrieve the distinct count of sessions, sum of amount, and distinct count of auditno from this table where trans_code is SBMN05RQ, ACWDXXRQ and RDXXXXRQ and where sessionID is the same for all 3.
SELECT COUNT(DISTINCT MAT.sessionID) as sessions, Sum(amount) as turnover, count(distinct audittrailno) as transactions FROM MBS_AuditTrail_Detail MATD LEFT JOIN MBS_AuditTrail MAT ON MATD.AUDITTRAILNO = MAT.auditNo WHERE MATD.trans_code LIKE 'SB%' and MATD.sessionid in (select sessionid from mbs_audittrail_detail where trans_code like 'AC%') and MAT.sessionid in (select sessionid from mbs_audittrail_detail where trans_code like 'RD%') group by MAT.accountno
The above is my current query. This query retrieves me the correct count of distinct sessionid, which in this case is 1. However, for my Sum(amount) as turnover and count(distinct audittrailno), it only returns me 1000 and 1 respectively. I understand the problem. It's because of my
WHERE MATD.trans_code LIKE 'SB%',
which only retrieves me the information of the first row. I know we can't [b]AND[b] the same column, so is there any suggestions?
I have a table called mbs_audittrail_detail, and it has the following columns,
Audittrailno | SessionID | Amount | Trans_Code
01 | 01 | 1000 | SBMN05RQ
02 | 01 | 2000 | ACWDXXRQ
03 | 01 | 0000 | RDXXXXRQ
04 | 02 | 1500 | SBMN05RQ
05 | 02 | 1500 | ACWDXXRQ
The problem I have is, I'd like to retrieve the distinct count of sessions, sum of amount, and distinct count of auditno from this table where trans_code is SBMN05RQ, ACWDXXRQ and RDXXXXRQ and where sessionID is the same for all 3.
SELECT COUNT(DISTINCT MAT.sessionID) as sessions, Sum(amount) as turnover, count(distinct audittrailno) as transactions FROM MBS_AuditTrail_Detail MATD LEFT JOIN MBS_AuditTrail MAT ON MATD.AUDITTRAILNO = MAT.auditNo WHERE MATD.trans_code LIKE 'SB%' and MATD.sessionid in (select sessionid from mbs_audittrail_detail where trans_code like 'AC%') and MAT.sessionid in (select sessionid from mbs_audittrail_detail where trans_code like 'RD%') group by MAT.accountno
The above is my current query. This query retrieves me the correct count of distinct sessionid, which in this case is 1. However, for my Sum(amount) as turnover and count(distinct audittrailno), it only returns me 1000 and 1 respectively. I understand the problem. It's because of my
WHERE MATD.trans_code LIKE 'SB%',
which only retrieves me the information of the first row. I know we can't [b]AND[b] the same column, so is there any suggestions?