Jes Sie

Jes Sie

  • 701
  • 1.2k
  • 264.5k

Aggregate in SQL SERVER

Mar 21 2017 6:22 AM
I have the query below: 
 
  1. SELECT sum(c.ConvertedNetPremium + c.ConvertedVAT + c.ConvertedRegistryFee) as Due  
  2.     from dbo.CashierSalesDetails c  
  3.   
  4. select sum(a.AmountPaid) as payment   
  5.     from dbo.tblAgentPaymentMonitoring a  
 My requirement is that, I should be able to deduct the "payment" from the "Due". below is the actual data of both tables
 
 
 
 
 I tried this code but the answer is wrong:
  1. SELECT a.TaxInvoiceNumber, sum(a.AmountPaid) as payment, sum(c.ConvertedNetPremium + c.ConvertedVAT + c.ConvertedRegistryFee) as Due,  
  2.   
  3.         sum(a.AmountPaid) - sum(c.ConvertedNetPremium + c.ConvertedVAT + c.ConvertedRegistryFee) as BAlance  
  4.   
  5. FROM     dbo.tblAgentPaymentMonitoring a INNER JOIN  
  6.                   dbo.CashierSalesDetails c ON a.TaxInvoiceNumber = c.TaxInvoiceNumber  
  7. GROUP BY a.TaxInvoiceNumber  
 
 
 

Answers (7)