I have a customer table custid,Trade,Amount three fields are there custid dont have unique value, now trade B & S values. I want to Show sum of amount of custid, for both the trade in one line Like cust id,Btrade,Bamount,Strade,Samount1 B 12000 S 5000 This One i tried but problem in thisselect a.Custid,a.TRADEID,sum(a.AMOUNT),b.TRADEID,sum(b.AMOUNT) from custmer a,custmer bwhere a.Custid = b.Custid and A.TRADEID = 'B' and b.TRADEID = 'S' group by a.Custid,a.TRADEID,b.Custid,b.TRADEID

ualike

 Profile Answers by ualike 

• Dec 5th, 2006

 

try this: SELECT zp.custid, Sum(IIf([trade]="A",[amnt])) AS SumOfTradeA, Sum(IIf([trade]="B",[amnt])) AS SumOfTradeB FROM zp GROUP BY zp.custid;

jamesravid

 Profile Answers by jamesravid 

• Dec 10th, 2006

 

Hi Try this query,Select custid, 'B' as BTrade, sum(decode(tradeid,'B',amount,0) Bamount 'S' as Strade, sum(decode(tradeid,'S',amount,0) Samountfrom customergroup by custidRegards,James.

Imran_Javed

 Profile Answers by Imran_Javed 

• May 9th, 2008

 

SELECT MIN(b.CUSTID) "cust id", MIN(b.TRADE) "Btrade", SUM(b.AMOUNT) "Bamount", MIN(s.trade) "Strade", MIN(s.amount) "Samount"FROM cust_t b, (SELECT MIN(CUSTID),MIN(TRADE) trade,SUM(AMOUNT) amount FROM cust_t WHERE trade ='s') s WHERE b.trade = 'b';