473,390 Members | 1,094 Online
Bytes | Software Development & Data Engineering Community
Post Job

Home Posts Topics Members FAQ

Join Bytes to post your question to a community of 473,390 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 4995
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,556 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

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

Similar topics

18
by: Roger Shrubber | last post by:
Hello all I have created a page which has several pictures on it. Each picture is surrounded by a frame made from actual photos of real picture frames, consisting of four corner tiles and four...
2
by: Ben Amada | last post by:
Hi group. I'm going to display a low resolution image in an HTML page. On the web server, I have a high resolution version of that image. If I display the high resolution image in the browser...
19
by: Lorenzo J. Lucchini | last post by:
My code contains this declaration: : typedef union { : word Word; : struct { : byte Low; : byte High; : } Bytes; : } reg;
16
by: msnews.microsoft.com | last post by:
I am teaching C# to my 11 year old child. One challenge is that all the C# books I own and that I have seen in bookstores are full of language that is not easily comprehended by a student at that...
1
by: Tommaso Caldarola | last post by:
I need to transfer big files (up to 10 Gb), now I'm using IIS via Remoting with chunk of bytes (up to 500Kb). In the following article: Middle-Tier Hosting: Enterprise Services, IIS, DCOM, Web...
2
by: Gordowey | last post by:
Hi all, I would like to ear your opinion about the best approach for and ASP.net with high workload traffic (High number of visitors) using SQL DB Consider the following scenario: - Website...
19
by: John | last post by:
The table below shows the execution time for this code snippet as measured by the unix command `time': for i in range(1000): time.sleep(inter) inter execution time ideal 0 ...
8
by: =?Utf-8?B?dGFuaQ==?= | last post by:
Hello there, one of my servers is running windows 2003 and IIS 6.0. We have big performance issues on that server and I found out that a high CPU usage iniciated by w3wp.exe causes the problem....
3
by: hamishmcgee | last post by:
Ok, so for a project at university I have to create a High Score table in C++ with Visual Studio. Just to let you know this is my first year at university and also my first time ever learning C++....
0
by: Laura Hood | last post by:
Availability for a brave new world With the advent of Software as a Service (SaaS) more businesses are relying on the ability to access their business data through web based applications. In...
0
by: taylorcarr | last post by:
A Canon printer is a smart device known for being advanced, efficient, and reliable. It is designed for home, office, and hybrid workspace use and can also be used for a variety of purposes. However,...
0
by: Charles Arthur | last post by:
How do i turn on java script on a villaon, callus and itel keypad mobile phone
0
by: ryjfgjl | last post by:
If we have dozens or hundreds of excel to import into the database, if we use the excel import function provided by database editors such as navicat, it will be extremely tedious and time-consuming...
0
by: emmanuelkatto | last post by:
Hi All, I am Emmanuel katto from Uganda. I want to ask what challenges you've faced while migrating a website to cloud. Please let me know. Thanks! Emmanuel
0
by: Hystou | last post by:
There are some requirements for setting up RAID: 1. The motherboard and BIOS support RAID configuration. 2. The motherboard has 2 or more available SATA protocol SSD/HDD slots (including MSATA, M.2...
0
marktang
by: marktang | last post by:
ONU (Optical Network Unit) is one of the key components for providing high-speed Internet services. Its primary function is to act as an endpoint device located at the user's premises. However,...
0
by: Hystou | last post by:
Most computers default to English, but sometimes we require a different language, especially when relocating. Forgot to request a specific language before your computer shipped? No problem! You can...
0
Oralloy
by: Oralloy | last post by:
Hello folks, I am unable to find appropriate documentation on the type promotion of bit-fields when using the generalised comparison operator "<=>". The problem is that using the GNU compilers,...
0
jinu1996
by: jinu1996 | last post by:
In today's digital age, having a compelling online presence is paramount for businesses aiming to thrive in a competitive landscape. At the heart of this digital strategy lies an intricately woven...

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.