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

Interview Candidate
Nov 20th, 2006
9
2569

Showing Answers 1 - 9 of 9 Answers

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';