Aggregate in SQL SERVER

Jes Sie

I have the query below:

SELECT sum(c.ConvertedNetPremium + c.ConvertedVAT + c.ConvertedRegistryFee) as Due
from dbo.CashierSalesDetails c
select sum(a.AmountPaid) as payment
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:

SELECT a.TaxInvoiceNumber, sum(a.AmountPaid) as payment, sum(c.ConvertedNetPremium + c.ConvertedVAT + c.ConvertedRegistryFee) as Due,
sum(a.AmountPaid) - sum(c.ConvertedNetPremium + c.ConvertedVAT + c.ConvertedRegistryFee) as BAlance
FROM dbo.tblAgentPaymentMonitoring a INNER JOIN
dbo.CashierSalesDetails c ON a.TaxInvoiceNumber = c.TaxInvoiceNumber
GROUP BY a.TaxInvoiceNumber

7 Replies

Know the answer? Post it — somebody with the same question will find it here.

Nitin Sontakke9y ago
Accepted answer
Got it. This is because we are joining the tables, which we should not. We will need to sum them separately as shown below:
declare @invoice_due table
(
   [TaxInvoiceNumber] [varchar](10)
  ,[NetPremium]     [decimal](18, 2)
  ,[VAT]            [decimal](18, 2)
  ,[RegistryFee]    [decimal](18, 2)
)

insert into @invoice_due
SELECT c.[TaxInvoiceNumber], sum(c.[ConvertedNetPremium]), sum(c.[ConvertedVAT]), sum(c.[ConvertedRegistryFee])
FROM [dbo].[CashierSalesDetails] c
GROUP BY c.[TaxInvoiceNumber]

declare @invoice_paid table
(
   [TaxInvoiceNumber] [varchar](10)
  ,[AmountPaid]     [decimal](18, 2)
)

insert into @invoice_paid
SELECT a.[TaxInvoiceNumber], sum(a.[AmountPaid]) as payment
FROM [dbo].[tblAgentPaymentMonitoring] a
GROUP BY a.[TaxInvoiceNumber]

select d.[TaxInvoiceNumber]
  ,d.[NetPremium]
  ,d.[VAT]
  ,d.[RegistryFee]
  ,d.[NetPremium] + d.[RegistryFee] + d.[VAT] [Due]
  ,p.[AmountPaid]
  ,((d.[NetPremium] + d.[RegistryFee] + d.[VAT]) - p.[AmountPaid]) [Balance]
from @invoice_due d
left outer join @invoice_paid p on d.[TaxInvoiceNumber] = p.[TaxInvoiceNumber]
where 1 = 1
--  and d.[TaxInvoiceNumber] = ?
You are better of writing a stored procedure for this and send a TaxInvoiceNumber as a parameter.
Nitin Sontakke9y ago
Just google "where 1 = 1" to learn all the debate about it....nothing great. Don't worry too much about it.
Jes Sie9y ago
Nitin Sontakke Thank you so much! Now I understand. May I ask why in the where clause you use 1 = 1?
Jes Sie9y ago
@Nitin Sontakke, Thanks for pointing that out. The negative already showed up. However, the data in tblAgentPaymentMonitoring is still wrong. The total actual payment made in tax invoice # 1 is only 1,000,000 but the query showed 6,000,000 (6 transactions). In tax invoice # 2 is correct since I have only 1 sample transaction made. Another error in data in tax invoice # 3 that shows 15,000,000 (10 transactions) when in fact the actual payment is only 1,500,000. The error only happens in tblAgentPaymentMonitoring. Any resolutions? Below are the actual sample data.
Nitin Sontakke9y ago
Your substraction is wrong. Should it not be as follows:
SELECT a.TaxInvoiceNumber
,sum(a.AmountPaid) as payment
,sum(c.ConvertedNetPremium + c.ConvertedVAT + c.ConvertedRegistryFee) as Due
,sum(c.ConvertedNetPremium + c.ConvertedVAT + c.ConvertedRegistryFee) - sum(a.AmountPaid) as BAlance
FROM dbo.tblAgentPaymentMonitoring a
INNER JOIN dbo.CashierSalesDetails c ON a.TaxInvoiceNumber = c.TaxInvoiceNumber
GROUP BY a.TaxInvoiceNumber
Jes Sie9y ago
Nitin Sontakke the joining column is TaxInvoiceNumber. Below is the script:
SELECT a.TaxInvoiceNumber, sum(a.AmountPaid) as payment, sum(c.ConvertedNetPremium + c.ConvertedVAT + c.ConvertedRegistryFee) as Due,

        sum(a.AmountPaid) - sum(c.ConvertedNetPremium + c.ConvertedVAT + c.ConvertedRegistryFee) as BAlance

FROM     dbo.tblAgentPaymentMonitoring a INNER JOIN
                  dbo.CashierSalesDetails c ON a.TaxInvoiceNumber = c.TaxInvoiceNumber
GROUP BY a.TaxInvoiceNumber
Nitin Sontakke9y ago
There must be some joining column. Which one is that. Give some sample raw data, preferably in script (plain text) format so that we can test our query.

Aggregate in SQL SERVER

Nitin Sontakke Thank you so much! Now I understand. May I ask why in the where clause you use 1 = 1?

Nitin Sontakke the joining column is TaxInvoiceNumber. Below is the script: