469,344 Members | 6,371 Online
Bytes | Developer Community
New Post

Home Posts Topics Members FAQ

Post your question to a community of 469,344 developers. It's quick & easy.

Sum is too high

Hello All,

I'm still a SQL newbie so I hope this question is easy. I have to sum some transactional data and do a report on it. I used the SUM() function and found out that my totals were too high. I did an experiment on my data and just pulled a months worth and checked the sums for a couple of data categories within the month. The sum for the second one I looked at where I used the sum function was higher than the sum of the individual records. Here are examples of my code, first with the SUM() function:

Expand|Select|Wrap|Line Numbers
  1. SET NOCOUNT ON
  2. SELECT COM_Number,
  3.         COM_Name, 
  4.         Sum(TRA_Amount) as Amount, 
  5.         Sum(ITR_Amount) AS Fraud, 
  6.         CAST('01 '+ RIGHT(CONVERT(CHAR(11),TRA_TransactionDate,113),8) AS DATETIME) AS 'TransMonth',
  7.         MerchantCategoryCode.MCC_Code, 
  8.         MerchantCategoryCode.MCC_Name
  9.     FROM ((((Transaction WITH (NOLOCK)
  10.     INNER JOIN (Company WITH (NOLOCK) 
  11.         INNER JOIN Account WITH (NOLOCK) ON Company.COM_CompanyId = Account.ACC_CompanyId) 
  12.     ON Transaction.TRA_AccountId = Account.ACC_AccountId) 
  13.         INNER JOIN Merchant WITH (NOLOCK) ON Transaction.TRA_MerchantId = Merchant.MER_MerchantId) 
  14.             INNER JOIN TransactionCode WITH (NOLOCK) ON TRA_TransactionCodeId = TransactionCode.TCO_TransactionCodeId) 
  15.                 INNER JOIN MerchantCategoryCode WITH (NOLOCK) ON Merchant.MER_MerchantCategoryCodeId = MerchantCategoryCode.MCC_MerchantCategoryCodeId) 
  16.                     LEFT JOIN IncidentTransaction WITH (NOLOCK) ON Transaction.TRA_MicroficheReferenceNumber = IncidentTransaction.ITR_ReferenceNumber
  17. WHERE     (Company.COM_Number ='655')
  18.     AND [TRA_TransactionDate] between '8/1/2009' and '8/31/2009' 
  19.     AND TransactionCode.TCO_Code 
  20.         In (
  21.         '001',
  22.         '006',
  23.         '008',
  24.         '031',
  25.         '036',
  26.         '038')
  27. Group BY Company.COM_Number,
  28.         Company.COM_Name, 
  29.         Transaction.TRA_TransactionDate, 
  30.         MerchantCategoryCode.MCC_Code, 
  31.         MerchantCategoryCode.MCC_Name    
And here's the SQL that returns the individual transactions. I copied the results of both to excel and sorted by the MCC_Code and did totals for each group of codes. The totals were less for the following SQL and I believe this one is right:

Expand|Select|Wrap|Line Numbers
  1. SET NOCOUNT ON
  2. SELECT Company.COM_Number,
  3.         Company.COM_Name, 
  4.         Transaction.TRA_Amount, 
  5.                 IncidentTransaction.ITR_Amount, 
  6.         CAST('01 '+ RIGHT(CONVERT(CHAR(11),TRA_TransactionDate,113),8) AS DATETIME) AS 'TransMonth',
  7.         MerchantCategoryCode.MCC_Code, 
  8.         MerchantCategoryCode.MCC_Name
  9.     FROM ((((Transaction WITH (NOLOCK)
  10.     INNER JOIN (Company WITH (NOLOCK) 
  11.         INNER JOIN Account WITH (NOLOCK) ON Company.COM_CompanyId = Account.ACC_CompanyId) 
  12.     ON Transaction.TRA_AccountId = Account.ACC_AccountId) 
  13.         INNER JOIN Merchant WITH (NOLOCK) ON Transaction.TRA_MerchantId = Merchant.MER_MerchantId) 
  14.             INNER JOIN TransactionCode WITH (NOLOCK) ON Transaction.TRA_TransactionCodeId = TransactionCode.TCO_TransactionCodeId) 
  15.                 INNER JOIN MerchantCategoryCode WITH (NOLOCK) ON Merchant.MER_MerchantCategoryCodeId = MerchantCategoryCode.MCC_MerchantCategoryCodeId) 
  16.                     LEFT JOIN IncidentTransaction WITH (NOLOCK) ON Transaction.TRA_MicroficheReferenceNumber = IncidentTransaction.ITR_ReferenceNumber
  17.  
  18. WHERE     (Company.COM_Number ='655')
  19.     AND [TRA_TransactionDate] between '8/1/2009' and '8/31/2009' 
  20.     AND TransactionCode.TCO_Code 
  21.         In (
  22.         '001',
  23.         '006',
  24.         '008',
  25.         '031',
  26.         '036',
  27.         '038')
  28. Group BY Company.COM_Number,
  29.         Company.COM_Name, 
  30.         Transaction.TRA_TransactionDate, 
  31.         Transaction.TRA_Amount, 
  32.         IncidentTransaction.ITR_Amount,  
  33.         MerchantCategoryCode.MCC_Code, 
  34.         MerchantCategoryCode.MCC_Name
My main question is, how do I get the totals for the results of first query with the SUM function to match the second one?

Thanks,

John
Aug 11 '10 #1
2 3959
code green
1,726 Expert 1GB
I've not studied your query too closely,
but what is likely happening is there are multiple joins to the same table so figures are being counted more than once.

Test the query with '*' in place of all the SELECT fields and without a GROUP BY.
This will show exactly what is being added.

This can be fixed with an extra condition inside the ON clause
Aug 11 '10 #2
NeoPa
32,183 Expert Mod 16PB
That's certainly good advice.

Another tip would be to use Common Table Expressions (CTEs) in your code. This just allows you to isolate elements of a complicated structure and deal with them more easily than looking through loads of parenthesised joins.

In your case I'd consider putting the items you need GROUPed within your CTE.

While developing your CTE it's a good idea to use :
Expand|Select|Wrap|Line Numbers
  1. WITH cteTest
  2. AS
  3. (
  4. SELECT ...
  5. FROM ...
  6. GROUP BY ...
  7. )
  8. SELECT *
  9. FROM   [cteTest]
Just to test that this part of the whole is working as you'd expect before building on it.

CTEs are little more than glorified subqueries, but they can be very useful for simplifying things for all that.

Good luck with your SQL :)
Aug 11 '10 #3

Post your reply

Sign in to post your reply or Sign up for a free account.

Similar topics

2 posts views Thread by Ben Amada | last post: by
19 posts views Thread by Lorenzo J. Lucchini | last post: by
16 posts views Thread by msnews.microsoft.com | last post: by
19 posts views Thread by John | last post: by
8 posts views Thread by =?Utf-8?B?dGFuaQ==?= | last post: by
3 posts views Thread by hamishmcgee | last post: by
1 post views Thread by CARIGAR | last post: by
reply views Thread by zhoujie | last post: by
reply views Thread by suresh191 | last post: by
By using this site, you agree to our Privacy Policy and Terms of Use.