The sum of all data other than the sum of the individual data sets

Question

What can cause SQL Server 2016 Express to return different results for the same dataset when you run individually or summarize everything at the same time?

All transactions that are sales:

SELECT sum(transactionamount) 
FROM [NewPOS].[dbo].[Transaction] 
WHERE TransactionDateTime >= '2016-11-26 00:00:00' 
  AND TransactionDateTime <= '2017-11-27 0:0:0' 
  AND transactiontype = 0

Results in 134253

All transactions that are sold with cash payment:

SELECT sum(transactionamount) 
FROM [NewPOS].[dbo].[Transaction] 
WHERE TransactionDateTime >= '2016-11-26 00:00:00' 
  AND TransactionDateTime <= '2017-11-27 0:0:0' 
  AND transactiontype = 0  
  AND TransactionId IN (SELECT TransactionId 
                        FROM payment 
                        WHERE payment.PaymentType = 0)

Results in 56318.5

All transactions that are sold with a credit card payment type:

SELECT sum(transactionamount) 
FROM [NewPOS].[dbo].[Transaction] 
WHERE TransactionDateTime >= '2016-11-26 00:00:00' 
  AND TransactionDateTime <= '2017-11-27 0:0:0' 
  AND transactiontype = 0  
  AND TransactionId IN (SELECT TransactionId 
                        FROM payment 
                        WHERE payment.PaymentType = 2)

Results in 54054.5

All transactions that are sold with a debit card payment type:

SELECT sum(transactionamount) 
FROM [NewPOS].[dbo].[Transaction] 
WHERE TransactionDateTime >= '2016-11-26 00:00:00' 
  AND TransactionDateTime <= '2017-11-27 0:0:0' 
  AND transactiontype = 0  
  AND TransactionId IN (SELECT TransactionId 
                        FROM payment 
                        WHERE payment.PaymentType = 3)

Results in 28738.5

Add 56318.5 + 54054.5 + 28738.5 to get 139111.5

139111.5 obviously = 134253

A total of 4858.5 is added to summarize these three transaction types.

To confirm that I did not miss the type of payment:

SELECT sum(transactionamount) 
FROM [NewPOS].[dbo].[Transaction] 
WHERE TransactionDateTime >= '2016-11-26 00:00:00' 
  AND TransactionDateTime <= '2017-11-27 0:0:0' 
  AND transactiontype = 0  
  AND TransactionId NOT IN (SELECT TransactionId 
                            FROM payment 
                            WHERE payment.PaymentType = 0 
                               OR payment.PaymentType = 2 
                               OR payment.PaymentType = 3)

, - , , , .

:

, :

SELECT sum(transactionAmount) 
FROM [NewPOS].[dbo].[Transaction] 
WHERE TransactionDateTime >= '2016-11-26 00:00:00' 
  AND TransactionDateTime <= '2017-11-27 0:0:0' 
  AND transactiontype = 0  
  AND TransactionId IN (SELECT TransactionId 
                        FROM payment 
                        WHERE payment.PaymentType = 0 
                           OR payment.PaymentType = 2 
                           OR payment.PaymentType = 3)

134253

2:

, - , , , , steenbergh:

-cash + debit ( " " + + .

cashback, .

Answer 1

payment TransactionID s. , , , .

select distinct TransactionID, count(*)
from Payment
group by TransactionID having count(*) > 1
order by 2 desc

, .

, payment , Transactions . , () , , .

---

Amount payment, JOIN:

SELECT sum(t.transactionamount) as Incorrect
, sum(p.amount) as Correct
FROM [NewPOS].[dbo].[Transaction] t
INNER JOIN [NewPOS].[dbo].[Payment] p on p.TransactionID = t.TransactionID
WHERE t.TransactionDateTime >= '2016-11-26 00:00:00' 
  AND t.TransactionDateTime <= '2017-11-27 0:0:0' 
  AND t.transactiontype = 0
  AND p.PaymentType = 0

, , :

SELECT distinct t.TransactionID
, p.PaymentType
, sum(p.amount) as [Amount paid with this type]
FROM [NewPOS].[dbo].[Transaction] t
INNER JOIN [NewPOS].[dbo].[Payment] p on p.TransactionID = t.TransactionID
WHERE t.TransactionDateTime >= '2016-11-26 00:00:00' 
  AND t.TransactionDateTime <= '2017-11-27 0:0:0' 
  AND t.transactiontype = 0

, , .

, :

JOINS . . om 'm, , YouTube.