0





7

Advertisement

What would cause SQL Server 2016 Express to return different results for the same data set when run individually vs summed all at once?

All transactions which 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 which are sales paid with payment type of cash:

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 which are sales paid with payment type of credit card:

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 which are sales paid with payment type of debit card:

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 is obviously != 134253

There is an extra 4858.5 in the summation of those three transaction types individually.

To validate I wasn't missing a 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 NOT IN (SELECT TransactionId 
                            FROM payment 
                            WHERE payment.PaymentType = 0 
                               OR payment.PaymentType = 2 
                               OR payment.PaymentType = 3)

It's probably something simple but I have been staring at these results, calculating things and can't figure out how these numbers are not adding up..

Edit:

Another verification query that the data being summed should be the same as individual queries:

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)

This query also results in 134253

Edit 2:

It appears that the check for multiple payment types failed to detect it -- I found the issue is most likely exactly that described in steenbergh's answer:

There are -cash and +debit transactions (cash back removes cash from drawer by adding a -cash payment, and a +debit payment for full amount + cash back..

This leads to really wacky numbers without filtering the cashback transaction numbers out, since there are transactions getting added twice once in cash, and once in debit..

Question author Richard | Source

Advertisement


0


Your payment table has multiple records for some of your TransactionIDs. This means that people paid in part with creditcard and in cash for the same transaction, for instance.

You can find this out with

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

This will show you all the Transactions where more than 1 method of payment was used.

This then influences your sum, because the payment table is only concerned with a part of those transactions, and the record in Transactions holds the full transaction amount. When you check that cash was used for (part of the) payment, and then take the full amount of the transaction, you also sum up the part paid for with a creditcard.


Assuming there's a column Amount or similar in the payment table, you could solve this problem with a 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

To further make a breakdown to demonstrate how these tables interact:

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

This query will list per transaction which types of payment were used, and for which amounts.

To further clarify, I've drawn you a picture: enter image description here

It's very important to have a good understanding of JOINS, in any given database. for any type of system. Read up om 'm, do courses, search on YouTube.

Answer author Steenbergh