Plz help

[r_p]

Hi,

A Database like this,

SCRIP_CODE SCRIP_NAME CLIENT_CODE QTY B_S
---------- ---------- ----------- ---------- ----------
500100 TISCO 47198 1000 BUY
500100 TISCO 47198 1000 SELL
500101 RELCAPITAL 47198 1000 BUY
500101 RELCAPITAL 47198 1000 SELL
500102 RELIANCE 47198 1000 BUY
500103 SATYAM 47198 2000 BUY
500101 RELCAPITAL 47198 1000 BUY
500101 RELCAPITAL 47198 1000 BUY


SELECT [scrip_code], [scrip_name], [client_code], max(IIf([b_s]='BUY',[qty],0))-max(IIf([b_s]='SELL',[qty],0)) AS net
FROM daily_trans
GROUP BY [scrip_code], [scrip_name], [client_code]
HAVING max(IIf(b_s='BUY',qty,0)) - max(IIf(b_s='SELL',qty,0)) <> 0;

Giving output as,

SCRIP_CODE SCRIP_NAME CLIENT_CODE NET
---------- ---------- ----------- ----------
500102 RELIANCE 47198 1000
500103 SATYAM 47198 2000

But i want the o/p as,

SCRIP_CODE SCRIP_NAME CLIENT_CODE NET
---------- ---------- ----------- ----------
500102 RELIANCE 47198 1000
500103 SATYAM 47198 2000
500101 RELCAPITAL 47198 2000

How can i solve this problem?

Thanks Rathish

 

[ByteMyzer]

The following modified SQL should return the desired results:

SELECT [scrip_code], [scrip_name], [client_code], sum(IIf([b_s]='BUY',[qty],0))-sum(IIf([b_s]='SELL',[qty],0)) AS net
FROM daily_trans
GROUP BY [scrip_code], [scrip_name], [client_code]
HAVING sum(IIf(b_s='BUY',qty,0)) - sum(IIf(b_s='SELL',qty,0)) <> 0;