472,143 Members | 1,579 Online
Bytes | Software Development & Data Engineering Community
Post +

Home Posts Topics Members FAQ

Join Bytes to post your question to a community of 472,143 software developers and data experts.

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 4665
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,499 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
reply views Thread by leo001 | last post: by

By using Bytes.com and it's services, you agree to our Privacy Policy and Terms of Use.

To disable or enable advertisements and analytics tracking please visit the manage ads & tracking page.