Member Avatar for anand01

hi All,
I have two tables
table-1:customeramount
table-2:customerpurchaseamount
table1 have customers bonus amount and table 2 i have customers purchase amount now i need to calculate balance bonus amount i tried with following query but It shows unexpected result , could any one guide me..

    SELECT CM.`custid`,
 SUM(CM.`amount`) - SUM(CP.`Amount`) as balance 
      FROM 
`customeramount` as CM JOIN customerpurchaseamount as CP 
       ON 
CM.`custid`= CP.`CustomerID` 
GROUP BY CM.`custid`
Edited by anand01
  • 2 Contributors
  • 4 Replies
  • 143 Views
  • 3 Days Discussion Span
  • Latest Post Latest Post by anand01
Member Avatar for smantscheff

Show some test data and the unexpected as well as the expected results.

Member Avatar for anand01

Thanks for your replay smantscheff,
my test data
table 1 : customeramount

custid             date           amount
1335279294867   2012-04-28          5
1335518740839   2012-04-28         124
1335357566946   2012-04-28         124
1335279294867   2012-05-17         10

Table2: customerpurchaseamount

TransactionID       CustomerID        Amount      Date
1                   1335518740839     5           2012-05-17
2                   1335518740839     10          2012-05-31

the result I am getting

1335518740839   233

But I need result set as

  1335279294867             5
  1335518740839           109
  1335357566946           124
  1335279294867           10
Edited by anand01
Member Avatar for smantscheff 
/*    
drop table if exists customeramount;
create customeramount (custid varchar(20), date date, amount integer);
insert into customeramount values (
('1335279294867','2012-04-28','5'),
('1335518740839','2012-04-28','124'),
('1335357566946','2012-04-28','124'),
('1335279294867','2012-05-17','10');


drop table if exists customerpurchaseamount;
create table customerpurchaseamount (TransactionID integer, CustomerID varchar(20), Amount integer, Date date);
insert into customerpurchaseamount values
('1','1335518740839','5','2012-05-17'),
('2','1335518740839','10','2012-05-31');
*/
select custid, ifnull(r,amount) as result
from
(

select a.custid, 
a.amount, a.amount - (
  select sum(b.Amount) 
  from customerpurchaseamount b 
  where a.custid=b.CustomerID
) as r
from customeramount a
) x
Member Avatar for anand01

thanks smantscheff,
Its working perfectly,You spent valuable to slove my query ,
thanks again

Be a part of the DaniWeb community

We're a friendly, industry-focused community of developers, IT pros, digital marketers, and technology enthusiasts meeting, networking, learning, and sharing knowledge.